按天对不同的用户登录进行分组
按天对不同的用户登录进行分组
提问于 2018-12-12 08:01:37
尝试获取每个不同用户每天的登录次数。但是，我的查询没有按日期分组：
DECLARE @StartDate AS Date
SET @StartDate = DATEADD(dd,-30,GETDATE())
SELECT CAST(ml.login_time AS date) AS Date_Login
,COUNT(DISTINCT ml.email) AS Total
FROM database.login AS ml
WHERE 1=1
AND ml.login_time > @StartDate
GROUP BY ml.login_time
ORDER BY ml.login_time DESC输出结果如下所示：
2018-12-11 1
2018-12-11 5
2018-12-11 2
2018-12-11 2
2018-12-11 1
2018-12-11 3
2018-12-10 1
2018-12-10 2
2018-12-10 3回答 3
Stack Overflow用户
回答已采纳
发布于 2018-12-12 08:05:58
对，是这样。您不是按日期分组，而是按时间分组。
您需要在group by和order by子句中重复转换：
SELECT CAST(ml.login_time AS date) as Date_Login,
COUNT(DISTINCT ml.email) as Total
FROM database.login AS ml
WHERE ml.login_time > @StartDate
GROUP BY CAST(ml.login_time AS date)
ORDER BY CAST(ml.login_time AS date) DESC;Stack Overflow用户
发布于 2018-12-12 08:05:43
您的GROUP BY ml.login_time子句与您的SELECT CAST(ml.login_time AS date)不匹配。因此，您可以按登录时间进行分组，并在CAST上获得重复的行。
尝试：
SELECT
CAST(ml.login_time AS date) AS Date_Login,
COUNT(DISTINCT ml.email) AS Total
FROM database.login AS ml
WHERE ml.login_time > @StartDate
GROUP BY CAST(ml.login_time AS date)
ORDER BY CAST(ml.login_time AS date) DESCStack Overflow用户
发布于 2018-12-12 08:06:31
您显示的是登录日期，但您是按日期时间分组的。
确保在GROUP BY中使用与SELECT中相同的CAST()：
DECLARE @StartDate AS Date
SET @StartDate = DATEADD(dd,-30,GETDATE())
SELECT CAST(ml.login_time AS date) AS Date_Login
,COUNT(DISTINCT ml.email) AS Total
FROM database.login AS ml
WHERE 1=1
AND ml.login_time > @StartDate
GROUP BY CAST(ml.login_time AS date)
ORDER BY ml.login_time DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53734208
复制相关文章
相似问题