sql server 2008选择问题
sql server 2008选择问题
提问于 2012-08-10 03:24:33
有两张桌子
Users:Id(PK int), Username(varchar (50))
Emails:Id(PK int), UserId(FK int), Subject(varchar(50)), Content(varchar(250)), SentAt(datetime)我必须显示每个用户发送了多少电子邮件，按天分组，按当天发送的电子邮件总数排序。我最好举个例子：
Date |User |Total
---------|-----------|-------
2012-4-5 |username1 |7
2012-4-5 |username2 |2
2012-4-2 |username1 |3
2012-3-24|username1 |12
2012-3-24|username5 |2我试过了，但显然不起作用。
ALTER PROCEDURE spGetStatistics
AS
SELECT e.SentAt, u.Username, ( SELECT COUNT(*) FROM Emails e2 WHERE e2.SentAt=e.SentAt AND e2.UserID=u.UserID ) AS Total
FROM Emails e INNER JOIN Users u ON e.UserID=u.UserID
GROUP BY e.SentAt
ORDER BY TotalLE：
Using the solution provided by Adrian which is:
SELECT CAST (e.SentAt AS date), u.Username, COUNT(*) AS Total
FROM Emails e INNER JOIN Users u ON e.UserID=u.UserID
GROUP BY CAST (e.SentAt AS date), u.Username
ORDER BY Total
I got this:
Date |User |Total
-----------|-----------|-------
2012-09-08 |username1 |1
2012-09-07 |username2 |2
2012-09-08 |username2 |2
instead of
Date |User |Total
-----------|-----------|-------
2012-09-08 |username2 |2
2012-09-08 |username1 |1
2012-09-07 |username2 |2
It seems to be working like this:
SELECT CAST (e.SentAt AS date), u.Username, COUNT(*) AS Total
FROM Emails e INNER JOIN Users u ON e.UserID=u.UserID
GROUP BY CAST (e.SentAt AS date), u.Username
ORDER BY CAST (e.SentAt AS date) DESC, Total DESC回答 2
Stack Overflow用户
回答已采纳
发布于 2012-08-10 03:27:01
这应该可以做到：
SELECT
cast(e.SentAt as Date) [Date],
u.Username,
COUNT(*) AS Total
FROM Emails e INNER JOIN Users u ON e.UserID=u.UserID
GROUP BY cast(e.SentAt as Date), u.Username
ORDER BY 3现在，这将隐藏未发送电子邮件的用户(count=0)。如果您想包含这些内容，您应该切换到以下内容：
SELECT
cast(e.SentAt as Date) [Date],
u.Username,
COUNT(e.Id) AS Total
FROM Users u LEFT JOIN Emails e ON e.UserID=u.UserID
GROUP BY cast(e.SentAt as Date), u.Username
ORDER BY 3更新
对于所需的顺序，您应该使用：
SELECT
cast(e.SentAt as Date) [Date],
u.Username,
COUNT(*) AS Total
FROM Emails e INNER JOIN Users u ON e.UserID=u.UserID
GROUP BY cast(e.SentAt as Date), u.Username
ORDER BY cast(e.SentAt as Date), Total DESCStack Overflow用户
发布于 2012-08-10 03:28:08
SELECT e.SentAt, u.Username, count(e.Id) AS Total
FROM Emails e
INNER JOIN Users u ON (e.UserID = u.UserID)
GROUP BY e.SentAt, u.Username
ORDER BY Total页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11890537
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号