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])

DATE_FORMAT

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