转载：SQL按照日、周、月、年统计数据的方法

转载源：http://www.jb51.net/article/42613.htm

SQL按照日、周、月、季度、年统计数据的方法

方式一：

--按日 
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])

--按周quarter 
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])

--按月 
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])

--按季 
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date]) 
 

--按年
select sum(consume),year([date]) from consume_record where  group by year([date])

方式二：

sqlserver 截取日期年份和月份使用datepart函数,函数使用方法如下：

一、函数功能：DATEPART() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

二、语法：DATEPART(datepart,date)

三、参数说明：date 参数是合法的日期表达式。datepart 参数可以是下列的值：

四、实例

1、截取年份：datepart(yy,'2017-1-1') 返回：2017

2、截取月份：datepart(mm,'2017-1-1') 返回：1

五、datepart函数返回的是整型数值，如果需要返回字符型，那么使用datename()函数,用法与datepart相同，只是返回数据类型不同。

 

常见错误

--选择列表中的列 'dbo.v_yjdatealljg.cjrq' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。
--把所有按组排序的依据都加上 yjxzqid, cjdd, ncpid, YEAR([cjrq]), month([cjrq])
--去掉农产品名称 ncpmc
SELECT yjxzqid, cjdd, ncpid, YEAR([cjrq]) as [year], month([cjrq]) as [month], avg( ttjg)as ttjg, avg(pfjg)as pfjg, avg(lsjg)as lsjg
FROM dbo.v_yjdatealljg group by yjxzqid, cjdd, ncpid, YEAR([cjrq]), month([cjrq])

 

DATE_FORMAT

1
2
3
select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

DATE_FORMAT(date,format) 
根据format字符串格式化date值。下列修饰符可以被用在format字符串中： 
%M 月名字(January……December) 
%W 星期名字(Sunday……Saturday) 
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。） 
%Y 年, 数字, 4 位 
%y 年, 数字, 2 位 
%a 缩写的星期名字(Sun……Sat) 
%d 月份中的天数, 数字(00……31) 
%e 月份中的天数, 数字(0……31) 
%m 月, 数字(01……12) 
%c 月, 数字(1……12) 
%b 缩写的月份名字(Jan……Dec) 
%j 一年中的天数(001……366) 
%H 小时(00……23) 
%k 小时(0……23) 
%h 小时(01……12) 
%I 小时(01……12) 
%l 小时(1……12) 
%i 分钟, 数字(00……59) 
%r 时间,12 小时(hh:mm:ss [AP]M) 
%T 时间,24 小时(hh:mm:ss) 
%S 秒(00……59) 
%s 秒(00……59) 
%p AM或PM 
%w 一个星期中的天数(0=Sunday ……6=Saturday ） 
%U 星期(0……52), 这里星期天是星期的第一天 
%u 星期(0……52), 这里星期一是星期的第一天 
%% 一个文字“%”。

 

 

 

本文只是记录在项目中用到的统计的SQL语句，记一笔以防忘了

 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
/// <summary>
   /// 获取统计数据
   /// </summary>
   /// <param name="CKEY">店面ckey</param>
   /// <param name="type">统计类型（日、周、月、年）</param>
   /// <returns></returns>
   [WebMethod(true)]
   public static string GetData3(string CKEY, string type)
   {
     StringBuilder strSql = new StringBuilder();
      
     #region SQL语句
 
     if (type == "0")
     {
       #region 日
       strSql.AppendFormat(" WITH  WeekDate ");
       strSql.AppendFormat("     AS ( SELECT  DATEADD(d, -DAY(GETDATE()) + 1, GETDATE()) AS riqi ");
       strSql.AppendFormat("       UNION ALL ");
       strSql.AppendFormat("       SELECT  riqi + 1 FROM   WeekDate ");
       strSql.AppendFormat("       WHERE  riqi + 1 <= ( SELECT  DATEADD(d, -DAY(GETDATE()), DATEADD(m, 1, GETDATE())) ) ");
       strSql.AppendFormat("      ) ");
       strSql.AppendFormat("  SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 ,DAY (CONVERT(CHAR(8), a.riqi, 112)) AS DDay, ");
       strSql.AppendFormat("      ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
       strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN NULL ");
       strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN ISNULL(tbB.日成交量, 0) ");
       strSql.AppendFormat("      END AS 日成交数量 , ");
       strSql.AppendFormat("      tbB.日实收金额 , ");
       strSql.AppendFormat("      CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN NULL ");
       strSql.AppendFormat("        WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("        THEN ISNULL(tbB.日实收金额, 0) ");
       strSql.AppendFormat("      END AS 日实收金额2 ");
       strSql.AppendFormat("  FROM  WeekDate a ");
       strSql.AppendFormat("      LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("                 FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("                 WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                      AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                      AND TargetDate = cus.TargetDate ");
       strSql.AppendFormat("                ) 日成交量 , ");
       strSql.AppendFormat("                ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                    FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                         FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                         WHERE   RechargDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                         UNION ALL ");
       strSql.AppendFormat("                         SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                         FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                         WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                              AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                        ) B ");
       strSql.AppendFormat("                   ), 0) AS 日实收金额 , ");
       strSql.AppendFormat("                TargetDate 日 ");
       strSql.AppendFormat("            FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("            WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
       strSql.AppendFormat("                AND MONTH(TargetDate) = MONTH(GETDATE()) ");
       strSql.AppendFormat("            GROUP BY TargetDate ");
       strSql.AppendFormat("           ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
       #endregion
     }
     else if (type == "1")
     {
       #region 周
       strSql.AppendFormat(" WITH  WeekDate ");
       strSql.AppendFormat("       AS ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) AS riqi ");
       strSql.AppendFormat("         UNION ALL ");
       strSql.AppendFormat("         SELECT  riqi + 1 FROM   WeekDate ");
       strSql.AppendFormat("         WHERE  riqi + 1 <= ( SELECT  DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 6) ) ");
       strSql.AppendFormat("        ) ");
       strSql.AppendFormat("    SELECT CONVERT(CHAR(8), a.riqi, 112) AS 日 , ");
       strSql.AppendFormat("        DATENAME(weekday,CONVERT(CHAR(8), a.riqi, 112)) DDay, ");
       strSql.AppendFormat("        ISNULL(tbB.日成交量, 0) AS 日成交量 , ");
       strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN NULL ");
       strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN ISNULL(tbB.日成交量, 0) ");
       strSql.AppendFormat("        END AS 日成交数量 , ");
       strSql.AppendFormat("        tbB.日实收金额 , ");
       strSql.AppendFormat("        CASE WHEN CONVERT(CHAR(8), a.riqi, 112) > CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN NULL ");
       strSql.AppendFormat("          WHEN CONVERT(CHAR(8), a.riqi, 112) <= CONVERT(CHAR(8), GETDATE(), 112) ");
       strSql.AppendFormat("          THEN ISNULL(tbB.日实收金额, 0) ");
       strSql.AppendFormat("        END AS 日实收金额2 ");
       strSql.AppendFormat("    FROM  WeekDate a ");
       strSql.AppendFormat("        LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("                   FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("                   WHERE   CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                        AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                        AND TargetDate = cus.TargetDate ");
       strSql.AppendFormat("                  ) 日成交量 , ");
       strSql.AppendFormat("                  ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                      FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND pay.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                           FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                           WHERE   RechargDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND recharge.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND payswim.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                           UNION ALL ");
       strSql.AppendFormat("                           SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                           FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                           WHERE   PayDate = cus.TargetDate ");
       strSql.AppendFormat("                                AND ware.CKEY = '{0}'", CKEY);
       strSql.AppendFormat("                          ) B ");
       strSql.AppendFormat("                     ), 0) AS 日实收金额 , ");
       strSql.AppendFormat("                  TargetDate 日 ");
       strSql.AppendFormat("              FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("              WHERE  DATEPART(wk, TargetDate) = DATEPART(wk, GETDATE()) ");
       strSql.AppendFormat("                  AND DATEPART(yy, TargetDate) = DATEPART(yy, GETDATE()) ");
       strSql.AppendFormat("              GROUP BY TargetDate ");
       strSql.AppendFormat("             ) AS tbB ON CONVERT(CHAR(8), a.riqi, 112) = tbB.日 ");
       #endregion
     }
     else if (type == "2")
     {
       #region 月
 
       strSql.AppendFormat("SELECT YearMonth.月 , ");
       strSql.AppendFormat("    tb.月成交量 , ");
       strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
       strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月成交量, 0) ");
       strSql.AppendFormat("    END AS 月成交数量 , ");
       strSql.AppendFormat("    tb.月实收总金额 , ");
       strSql.AppendFormat("    CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
       strSql.AppendFormat("      WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月实收总金额, 0) ");
       strSql.AppendFormat("    END AS 月实收总金额2 ");
       strSql.AppendFormat(" FROM   ( SELECT 1 AS 月 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ");
       strSql.AppendFormat("       UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 ");
       strSql.AppendFormat("      ) AS YearMonth ");
       strSql.AppendFormat("    LEFT JOIN ( SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("               FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("               WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                    AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("                    AND MONTH(TargetDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("              ) 月成交量 , ");
       strSql.AppendFormat("              ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("                  FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   PaymentContent AS pay ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("                       FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("                       WHERE   MONTH(RechargDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                       UNION ALL ");
       strSql.AppendFormat("                       SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("                       FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("                       WHERE   MONTH(PayDate) = MONTH(cus.TargetDate) ");
       strSql.AppendFormat("                            AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("                      ) B ");
       strSql.AppendFormat("                 ), 0) AS 月实收总金额 , ");
       strSql.AppendFormat("              MONTH(TargetDate) 月 ");
       strSql.AppendFormat("          FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("          WHERE  YEAR(TargetDate) = YEAR(GETDATE()) ");
       strSql.AppendFormat("          GROUP BY MONTH(cus.TargetDate) ");
       strSql.AppendFormat("         ) AS tb ON YearMonth.月 = tb.月 ");
       #endregion
     }
     else if (type == "3")
     {
       #region 年
       strSql.AppendFormat("SELECT ( SELECT  COUNT(1) ");
       strSql.AppendFormat("       FROM   dbo.CustomerBase base ");
       strSql.AppendFormat("       WHERE   CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("            AND " + impomo.TotalConsumptionMon + " > 0 ");
       strSql.AppendFormat("            AND YEAR(TargetDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("      ) 年成交量 , ");
       strSql.AppendFormat("      CONVERT(NVARCHAR(20),CONVERT(DECIMAL(18,2),ISNULL(( SELECT SUM(Total) ");
       strSql.AppendFormat("          FROM  ( SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   PaymentContent AS pay ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND pay.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(RecMoney, 0))) AS Total ");
       strSql.AppendFormat("               FROM   dbo.CardRecharge8 AS recharge ");
       strSql.AppendFormat("               WHERE   YEAR(RechargDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND recharge.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( pc1 + pc2 + pc3 + pc4 + pc5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   dbo.PaymentSwimming AS payswim ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND payswim.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("               UNION ALL ");
       strSql.AppendFormat("               SELECT  SUM(CONVERT(FLOAT, ISNULL(( wp1 + wp2 + wp3 + wp4 + wp5 ), 0))) AS Total ");
       strSql.AppendFormat("               FROM   WarePaymentContent AS ware ");
       strSql.AppendFormat("               WHERE   YEAR(PayDate) = YEAR(cus.TargetDate) ");
       strSql.AppendFormat("                    AND ware.CKEY = '{0}' ", CKEY);
       strSql.AppendFormat("              ) B ");
       strSql.AppendFormat("         ), 0))) AS 年实收总金额 , ");
       strSql.AppendFormat("      YEAR(TargetDate) 年 ");
       strSql.AppendFormat("  FROM  dbo.CustomerBase cus ");
       strSql.AppendFormat("  GROUP BY YEAR(TargetDate) ");
       #endregion
     }
 
     #endregion
 
     DataTable table = DBHelper.GetDateTable(strSql.ToString());
     string rs = Newtonsoft.Json.JsonConvert.SerializeObject(table);
posted @ 2017-10-11 18:08  hao_1234_1234  阅读(4037)  评论(0)  编辑  收藏  举报