[sql]SELECT * FROM events WHERE daydate>=1 AND daydate<=31 GROUP BY daydate[/sql] Данный запрос выберет по 1 му событию на каждый день. Как выбрать по 3 события? Спасибо.
Baltazar5000 [sql]SELECT * FROM events WHERE daydate>=1 AND daydate<=31 GROUP BY daydate HAVING count(*) > 2[/sql]
Не подходит. Это доп условие чтобы количество элементов в группе было больше 2х. А мне надо вывести именно по 2 или 3 элемента с каждой группы. Можно не используя GROUP.
Поскольку MySQL 5 не поддерживает limit в выражения IN задача просто не решается. Есть решение в лоб, но учтите, оно убьёт вашу БД. Для выбора двух строк в день: [sql]select * from events where id in ( select id from events group by day union select id from events where id not in (select id from events t1 group by day) group by day ) [/sql] для трёх: [sql]select * from events where id in( select id from events group by day union select id from events where id not in (select id from events t1 group by day) group by day union select id from events where id not in ( select id from events group by day union select id from events where id not in (select id from events t1 group by day) group by day ) group by day ) [/sql] исходные данные: [sql] CREATE TABLE `events` ( `id` int(11) NOT NULL AUTO_INCREMENT, `day` int(11) NOT NULL, `text` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ); INSERT INTO `events` VALUES (1,1,'some1'),(2,1,'some2'),(3,1,'some3'),(4,1,'some4'),(5,1,'some5'),(6,1,'some6'),(7,2,'some7'),(8,2,'some8'),(9,3,'some9'),(10,4,'some10'),(11,4,'some11'),(12,4,'some12'),(13,4,'some13'),(14,4,'some14'),(15,4,'some15'),(16,4,'some16'),(17,4,'some17'),(18,4,'some18'),(19,4,'some19'),(20,4,'some20'),(21,4,'some21'); [/sql] Код (Text): mysql> select * from events; +----+-----+--------+ | id | day | text | +----+-----+--------+ | 1 | 1 | some1 | | 2 | 1 | some2 | | 3 | 1 | some3 | | 4 | 1 | some4 | | 5 | 1 | some5 | | 6 | 1 | some6 | | 7 | 2 | some7 | | 8 | 2 | some8 | | 9 | 3 | some9 | | 10 | 4 | some10 | | 11 | 4 | some11 | | 12 | 4 | some12 | | 13 | 4 | some13 | | 14 | 4 | some14 | | 15 | 4 | some15 | | 16 | 4 | some16 | | 17 | 4 | some17 | | 18 | 4 | some18 | | 19 | 4 | some19 | | 20 | 4 | some20 | | 21 | 4 | some21 | +----+-----+--------+ 21 rows in set (0.00 sec) mysql> select * from events -> where id in ( -> select id from events group by day union -> select id from events where id not in (select id from events t1 group by day) group by day -> ) -> ; +----+-----+--------+ | id | day | text | +----+-----+--------+ | 1 | 1 | some1 | | 2 | 1 | some2 | | 7 | 2 | some7 | | 8 | 2 | some8 | | 9 | 3 | some9 | | 10 | 4 | some10 | | 11 | 4 | some11 | +----+-----+--------+ 7 rows in set (0.02 sec) mysql> select * from events -> where id in( -> select id from events group by day union -> select id from events where id not in (select id from events t1 group by day) group by day union -> select id from events where id not in ( -> select id from events group by day union -> select id from events where id not in (select id from events t1 group by day) group by day -> ) group by day -> ) -> ; +----+-----+--------+ | id | day | text | +----+-----+--------+ | 1 | 1 | some1 | | 2 | 1 | some2 | | 3 | 1 | some3 | | 7 | 2 | some7 | | 8 | 2 | some8 | | 9 | 3 | some9 | | 10 | 4 | some10 | | 11 | 4 | some11 | | 12 | 4 | some12 | +----+-----+--------+ 9 rows in set (0.48 sec) Несмотря на то, что это грубое решения для MySQL, оно может помочь вам найти более простое и элегантное решение.
Что касается меня, то считаю следующий вариант правильным: [sql]SELECT * FROM ( SELECT day FROM ( SELECT distinct day, c1 FROM `events` t2 CROSS JOIN ( SELECT 1 as c1 UNION SELECT 2 UNION SELECT 3 ) t3 ) t5 ) t4 LEFT JOIN `events` e1 ON e1.day = t4.day [/sql] Но MySQL категорически против и плавно превращает LEFT JOIN в RIGHT JOIN Второй вариант MySQL тоже отверг: [sql]SELECT * FROM events t1 WHERE id in (SELECT id FROM events t2 WHERE t2.day = t1.day LIMIT 3)[/sql]