MySQL GROUP BY 查询只返回一行 [英] MySQL GROUP BY query returns only one row

问题描述

    $q2 =   "SELECT startdate, 
             GROUP_CONCAT(summary separator '<br />') as summary, 
             GROUP_CONCAT(TIME(startdate)) AS starttime, 
             GROUP_CONCAT(TIME(enddate)) AS endtime
             FROM oc_clndr_objects
             WHERE calendarid = $calID
             GROUP BY DATE(startdate)
             ORDER BY startdate ASC";

上述查询仅返回一个结果(如预期的那样，一个日期的 3 个事件) - 但是每天重复 1 个事件，并且查询不会超出第一个开始日期.

This above query is only returning one result (3 events for one 1 date as expected) - however there is 1 event that is repeating daily, and the query does not go beyond the first startdate.

我知道它与 GROUP BY 子句和聚合函数有关，但我无法理解它.- 输出可见于 http://www.deliriousdreams.co.uk/ - "DJ Schedule测试"

I know it has something to do with the GROUP BY clause and something about Aggregation functions but I cannot wrap my head around it. - The output is visible at http://www.deliriousdreams.co.uk/ - "DJ Schedule Test"

如果您能帮助我们纠正稍微非法的 SQL 查询，我们将不胜感激.

Any help with correcting the slightly illegal SQL query would be much appreciated.

好的，时髦的人，修改为 DATE(startdate) AS startdate.一个小问题，之后我希望能够按 DATE 然后按 TIME(最早的优先)对它们进行排序 - 显然 DATE 和 TIME 都是从开始日期(DATETIME 戳)派生的 - 这是否可以通过修改上述查询或将我必须做一些 PHP 巫术?

Alright, groovy people, amended to DATE(startdate) AS startdate. One little niggle, after that I would like to be able to sort them by DATE and then TIME (earliest first) - Obviously both DATE and TIME are derived from the startdate (DATETIME stamp) - is this possible by amending the above query or would I have to do some PHP witchcraft?

推荐答案

大功告成！这是我一直在寻找的解决方案...

And it is done! This is the solution I was looking for...

SELECT
       DATE(startdate) AS startdate, 
       GROUP_CONCAT(summary separator '<br />') as summary, 
       GROUP_CONCAT(TIME(startdate)) AS starttime, 
       GROUP_CONCAT(TIME(enddate)) AS endtime
FROM
       (SELECT * FROM oc_clndr_objects ORDER BY startdate ASC) AS temp
WHERE calendarid = $calID
GROUP BY DATE(startdate)
ORDER BY DATE(startdate) ASC

这篇关于MySQL GROUP BY 查询只返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！