--
-- Структура таблицы `k-ts`
--

CREATE TABLE IF NOT EXISTS `k-ts` (
  `ids` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `s` varchar(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `kol` mediumint(8) unsigned NOT NULL COMMENT 'Количество',
  `f` tinyint(3) unsigned NOT NULL COMMENT 'Состояние',
  PRIMARY KEY (`ids`),
  UNIQUE KEY `s` (`s`),
  KEY `f` (`f`),
  KEY `kol` (`kol`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Слова на кириллице' AUTO_INCREMENT=9356184 ;

-- --------------------------------------------------------

--
-- Структура таблицы `k-t_s`
--

CREATE TABLE IF NOT EXISTS `k-t_s` (
  `id_sv` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `id_n` int(10) unsigned zerofill NOT NULL,
  `id_s` int(10) unsigned zerofill NOT NULL,
  PRIMARY KEY (`id_sv`),
  UNIQUE KEY `sost` (`id_n`,`id_s`),
  KEY `id_s` (`id_s`),
  KEY `id_n` (`id_n`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Связи для кириллицы' AUTO_INCREMENT=9159504 ;

-- --------------------------------------------------------

select `k-ts`.`s`, count(*)
	  from (
		select `k-t_s`.`id_n`
		from  `k-ts`
		join `k-t_s` on `k-t_s`.`id_s` = `k-ts`.`ids`
		where `k-ts`.`s` in ("фон","белый","город")
		group by `k-t_s`.`id_n` having count(/*distinct*/ `k-t_s`.`id_s`) = 3
		) `g`
	  join `k-t_s` on `k-t_s`.`id_n` = `g`.`id_n`
	  join `k-ts` on `k-ts`.`ids` = `k-t_s`.`id_s`
	  where `k-ts`.`f` in (0, 1, 7)
	  group by `k-t_s`.`id_s`, `k-ts`.`s`
	  order by count(*) desc, `k-ts`.`s` LIMIT 0, 160
	  ;

select `k-ts`.`s`, count(*)
from `k-t_s`
join `k-ts` on `k-ts`.`ids` = `k-t_s`.`id_s` and `k-ts`.`f` in (0, 1, 7)
where exists ( select * from  `k-ts` where `k-ts`.`ids` = `k-t_s`.`id_s` and `k-ts`.`s` = "фон")
  and exists ( select * from  `k-ts` where `k-ts`.`ids` = `k-t_s`.`id_s` and `k-ts`.`s` = "белый")
  and exists ( select * from  `k-ts` where `k-ts`.`ids` = `k-t_s`.`id_s` and `k-ts`.`s` = "город")
group by `k-ts`.`s`
order by count(*) desc
LIMIT 0, 160;