MySQL 计数 DISTINCT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5737628/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL COUNT DISTINCT
提问by Mike
I'm trying to collect the number of distinct visits in my cp yesterday, then count them.
我试图收集昨天在我的 cp 中的不同访问次数，然后计算它们。
SELECT
DISTINCT `user_id` as user,
`site_id` as site,
`ts` as time
FROM
`cp_visits`
WHERE
ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
For some reason this is pulling multiple results with the same site id....how do i only pull and count the distinct site_id cp logins?
出于某种原因，这是使用相同的站点 id 提取多个结果....我如何只提取和计算不同的 site_id cp 登录？
回答by ypercube??
Select
Count(Distinct user_id) As countUsers
, Count(site_id) As countVisits
, site_id As site
From cp_visits
Where ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
Group By site_id
回答by RichardTheKiwi
Overall
总体
SELECT
COUNT(DISTINCT `site_id`) as distinct_sites
FROM `cp_visits`
WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
Or per site
或每个站点
SELECT
`site_id` as site,
COUNT(DISTINCT `user_id`) as distinct_users_per_site
FROM `cp_visits`
WHERE ts >= DATE_SUB(NOW(), INTERVAL 1 DAY)
GROUP BY `site_id`
Having the timecolumn in the result doesn't make sense - since you are aggregating the rows, showing one particular timeis irrelevant, unless it is the minor maxyou are after.
time结果中的列没有意义 - 因为您正在聚合行，所以显示一个特定的内容time是无关紧要的，除非它是min或者max您在之后。
回答by Byron Whitlock
You need to use a group by clause.
您需要使用 group by 子句。
SELECT site_id, MAX(ts) as TIME, count(*) group by site_id
