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

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语句，记一笔以防忘了

 /// 

    /// 获取统计数据
    /// 
    /// 店面ckey
    /// 统计类型（日、周、月、年）
    /// 
    [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);
      return rs;
    }