mysql - SQL - 向 select 子句添加计数

Question

我有这个查询来显示我网站上的趋势(搜索最多)名称列表:

SELECT name, COUNT(*) AS total_trends
FROM trending_names
WHERE dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' // -7 days to Now()
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 10;

...这是我打印到屏幕上的结果:(数字代表搜索量)

Angelina Jolie        31,293
Rihanna               26,722
Lindsay Lohan         18,351
Brad Pitt             11,901

我现在想将数字更改为百分比；所以我确实需要获取过去 7 天内所有热门名称的总数，以计算正确的百分比。

有没有一种方法可以在不添加额外查询的情况下向该查询添加总计数？

Answer 1

您可以在单个查询中执行:

尝试以下:

SELECT name, COUNT(*) AS total_trends,
sum(if(dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' ,1,0)) as total_last_7_days,
((sum(if(dateTime BETWEEN '"&fromDate&"' AND '"&toDate&"' ,1,0)) /COUNT(*) ) *100)
as percentage                          // if you want to get only percentage
FROM trending_names
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 10;