SQL Server 2008中选择问题
问题描述:
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 Total
LE:
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
答
这应该这样做:
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
现在,这个隐藏谁送没有电子邮件的用户(计数= 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 DESC
答
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
我看到您的更新。这是因为您在您的问题中陈述过,您希望结果“通过当天发送的全部电子邮件订购” – 2012-08-09 19:56:52