如何在 SQL Server 中创建分组的每日、每周和每月报告，包括计算字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24610143/
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
How to create grouped daily,weekly and monthly reports including calculated fields in SQL Server
提问by andym0908
I'm using SQL Server (2012), and using the two (simplified) tables below, how do I create 3 separate reports (daily, weekly and monthly) and include the following calculated fields:
我正在使用 SQL Server (2012)，并使用下面的两个（简化）表，如何创建 3 个单独的报告（每日、每周和每月）并包含以下计算字段：
1. new users created in this period
2. total number of users at this time
**Users**
userID int
name varchar(80)
userCreated datetime
**Orders**
orderID int
userID int
orderCreated datetime
I've been messing around with this code:
我一直在搞乱这段代码：
SELECT CAST(DATEPART(dd,userCreated) as VARCHAR(2)) + '/' + CAST(DATEPART(mm,userCreated) AS VARCHAR(2)) + '/' + CAST(DATEPART(yyyy,userCreated) AS VARCHAR(4)) [Date],
count(*) newSignUps,
(select count(*) from users u2 WHERE u2.userCreated < u1.userCreated)
FROM users u1
WHERE userCreated BETWEEN '05/01/2014 00:00:00.000' and '05/31/2014 23:59:59.000'
GROUP BY DATEPART(dd,userCreated), DATEPART(mm,userCreated), DATEPART(yyyy,userCreated),userCreated
But to show anything, it needs the "userCreated" field added to the grouping...
但是要显示任何内容，它需要将“userCreated”字段添加到分组中...
For the reports I need to show:
对于我需要显示的报告：
Daily:
日常的：
date new sign ups users in system
17/03/2013 10 100
18/03/2013 4 104
19/03/2013 8 112
Weekly:
每周：
week
13 8 40
14 2 42
15 5 47
Monthly:
每月：
Jan 3 54
Feb 9 63
Mar 2 65
I hope this makes sense? Thank you...
我希望这是有道理的？谢谢...
回答by schlonzo
I'm not sure if I understood your question correctly, but this gives you all the users created per day:
我不确定我是否正确理解了您的问题，但这为您提供了每天创建的所有用户：
SELECT year(userCreated), month(userCreated), day(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated), day(userCreated)
this one by month:
这个按月：
SELECT year(userCreated), month(userCreated), count(*)
FROM Users
GROUP BY year(userCreated), month(userCreated)
and this one by week:
这是一个星期的：
SELECT year(userCreated), datepart(week, userCreated), count(*)
FROM Users
GROUP BY year(userCreated), datepart(week, userCreated)
Edit according to you the missing total field I give you here the example for the month query:
根据您编辑缺失的总计字段，我在此处为您提供月份查询的示例：
SELECT year(userCreated), month(userCreated), count(*) AS NewCount,
(SELECT COUNT(*) FROM Users u2 WHERE
CAST(CAST(year(u1.userCreated) AS VARCHAR(4)) + RIGHT('0' + CAST(month(u1.userCreated) AS VARCHAR(2)), 2) + '01' AS DATETIME) > u2.userCreated) AS TotalCount
FROM Users u1
GROUP BY year(userCreated), month(userCreated)
Hope this helps for the other two queries.
希望这有助于其他两个查询。
回答by sumit
DAILY
日常的
select count(UserId),userCreated
from User WHERE CONDITION group by CreatedOn
MONTHLY
每月
select count(UserId),
LEFT(CONVERT(varchar, userCreated ,112),6) from User
WHERE CONDITION group by LEFT(CONVERT(varchar, userCreated ,112),6)
OR
或者
select count(UserId),
month(userCreated )
from User group by month(userCreated )
WEEKLY
每周
select count(UserId),
DATEPART( wk, userCreated)
from User WHERE CONDITION group by DATEPART( wk, userCreated )
FOR BOTH NEW and EXISTING USER COUNT (i did for monthly)
对于新用户和现有用户计数（我每月都这样做）
select new,
(select count(UserId) from User where month(userCreated)<=monthwise) as total,
monthwise
FROM (
select count(UserId) as new,
month(userCreated)as monthwise from User group by month(userCreated)
)tmp
