SQLize | PHPize | SQLtest

CREATE TABLE `keywords` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `profession` varchar(255) NOT NULL, `region` varchar(255) NOT NULL, `rating` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `keywords_rating_index` (`name`,`region`,`profession`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `keywords` (`name`, `profession`, `region`, `rating`) VALUES ("git", "front-end developer", "москва", 10), ("git", "дизайнер", "москва", 3), ("git", "backend developer", "москва", 12), ("git", "backend developer", "новосибирск", 7), ("MySql", "backend developer", "новосибирск", 7), ("MySql", "аналитик", "новосибирск", 5); CREATE TABLE `salaries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `keywordId` int(11) NOT NULL, `salary` int(11) NOT NULL, `freq` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fk_keywordId` (`keywordId`), CONSTRAINT `fk_keywordId` FOREIGN KEY (`keywordId`) REFERENCES `keywords` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `salaries` (`keywordId`, `salary`, `freq`) VALUES (1, 100000, 3), (1, 150000, 2), (1, 200000, 1), (2, 100000, 5), (2, 130000, 3), (2, 150000, 7), (3, 100000, 1), (3, 150000, 7), (3, 170000, 4), (4, 100000, 5), (4, 125000, 5), (4, 150000, 2), (5, 100000, 8), (5, 150000, 1); select name, rating, group_concat(salary) from ( select name, group_concat(keywords.id) as keywords, sum(rating) as rating from keywords where profession="backend developer" group by name order by sum(rating) DESC limit 10) as t join salaries on find_in_set(keywordId, keywords) group by name, rating;