mysql - SQL:更改查询结构

Question

我有一个查询成功返回单个值，其中“day”在查询中被硬编码，如下所示:

SELECT MAX(theCount) FROM
    (SELECT FK_Hour, Count(FK_Hour) As theCount FROM
        (Select FK_Hour
        From slottime
        INNER JOIN time ON slottime.FK_Hour = time.Hour
        WHERE FK_Hour IN 
            (SELECT time.Hour FROM time WHERE time.day=0 )
        ) As C
        GROUP By FK_Hour
    ) AS counts;

我正在尝试删除此硬编码，以便两列；即day:theCount 被返回。

我已经尝试过

SELECT MAX(theCount), day FROM
(SELECT FK_Hour, day As day, Count(FK_Hour) As theCount FROM
    (Select slottime.FK_Hour, time.day
    From slottime
    INNER JOIN time ON slottime.FK_Hour = time.Hour
    ) As C
    GROUP By FK_Hour
) AS counts
GROUP By day;

然后它就会执行。但是它返回的值显然是不正确的(与正在查询的表中的数据没有明显的相关性)

Answer 1

在第一个示例中，如果我没看错的话，您将获得每个 fk_hour 值的 day = 0 的记录数，并从中选择最大计数。

要对所有日期执行此操作，请首先编写聚合查询来获取每一天和小时对的计数，如下所示:

SELECT t.day, t.hour, COUNT(*) AS numRecords
FROM time t
GROUP BY t.day, t.hour;

一旦你有了这个，你就可以通过使用限制 1 来获得最大值:

SELECT t.day, t.hour, COUNT(*) AS numRecords
FROM time t
GROUP BY t.day, t.hour
ORDER BY COUNT(*) DESC
LIMIT 1;

此查询将返回一行，并告诉您记录数量最多的日期和时间。