本文介绍了MySQL嵌套选择查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述



$ 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嵌套选择查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 08:59
查看更多