问题描述
$ p $
SELECT MIN(`date`),`player_name`
FROM`player_playtime`
GROUP BY`player_name`
然后我需要使用这个结果在下面的查询中:
SELECT DATE(`date`),COUNT(DISTINCT`player_name`)
FROM` player_playtime / *在这里使用以前的查询结果* /`
GROUP BY DATE(`date`)DESC LIMIT 60
我怎么去做这件事?
您只需将第一个查询写为子查询派生表),在括号内,为它选择一个别名( t
),并为列别名。
DISTINCT
也可以安全地删除,因为内部的 GROUP BY
使其成为多余的:
SELECT DATE(`date`)AS`date`,COUNT(`player_name`)AS`player_count`
FROM(
SELECT MIN('date`)AS'date`,`pla yer_name`
FROM`player_playtime`
GROUP BY`player_name`
)AS t
GROUP BY DATE(`date`)DESC LIMIT 60;
由于 COUNT
现在显而易见,只计算派生表的行,您可以用 COUNT(*)
替换它,并进一步简化查询:
<$ ($($'$'))作为日期
从玩家_播放时间'
GROUP BY player_name
)AS t
GROUP BY t.date DESC LIMIT 60;
Ok, so I have the following query:
SELECT MIN(`date`), `player_name`
FROM `player_playtime`
GROUP BY `player_name`
I then need to use this result inside the following query:
SELECT DATE(`date`) , COUNT(DISTINCT `player_name`)
FROM `player_playtime /*Use previous query result here*/`
GROUP BY DATE( `date`) DESC LIMIT 60
How would I go about doing this?
You just need to write the first query as a subquery (derived table), inside parentheses, pick an alias for it (t
below) and alias the columns as well.
The DISTINCT
can also be safely removed as the internal GROUP BY
makes it redundant:
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 ;
Since the COUNT
is now obvious that is only counting rows of the derived table, you can replace it with COUNT(*)
and further simplify the query:
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 ;
这篇关于MySQL嵌套选择查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!