MySQL嵌套 Select 查询

发布于11月14日

好的，我有以下问题:

SELECT MIN(`date`), `player_name`
FROM `player_playtime`
GROUP BY `player_name`

然后，我需要在以下查询中使用此结果:

SELECT DATE(`date`) , COUNT(DISTINCT  `player_name`)
FROM  `player_playtime /*Use previous query result here*/`
GROUP BY DATE( `date`) DESC LIMIT 60

我该怎么做呢？

推荐答案

您只需要将第一个查询作为子查询(派生表)写入括号中，为它 Select 一个别名(下面的t)，并为列添加别名.

由于内部GROUP BY使其冗余，DISTINCT也可以安全拆除:

SELECT DATE(`date`) AS `date` , COUNT(`player_name`) AS `player_count`
FROM (
    SELECT MIN(`date`) AS `date`, `player_name`
    FROM `player_playtime`
    GROUP BY `player_name`
) AS t
GROUP BY DATE( `date`) DESC LIMIT 60 ;

由于COUNT现在很明显只对派生表的行进行计数，因此可以将其替换为COUNT(*)，并进一步简化查询:

SELECT t.date , COUNT(*) AS player_count
FROM (
    SELECT DATE(MIN(`date`)) AS date
    FROM player_playtime
    GROUP BY player_name
) AS t
GROUP BY t.date DESC LIMIT 60 ;