SQL按照日、周、月、年統(tǒng)計(jì)數(shù)據(jù)的方法分享

來源：懂視網(wǎng) 責(zé)編：小采時(shí)間：2020-11-09 07:06:04

SQL按照日、周、月、年統(tǒng)計(jì)數(shù)據(jù)的方法分享

SQL按照日、周、月、年統(tǒng)計(jì)數(shù)據(jù)的方法分享:--按日 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_rec

推薦度：

點(diǎn)擊下載本文 文檔為doc格式

導(dǎo)讀SQL按照日、周、月、年統(tǒng)計(jì)數(shù)據(jù)的方法分享:--按日 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_rec

--按日
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)
根據(jù)format字符串格式化date值。下列修飾符可以被用在format字符串中：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英語前綴的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 數(shù)字, 4 位
%y 年, 數(shù)字, 2 位
%a 縮寫的星期名字(Sun……Sat)
%d 月份中的天數(shù), 數(shù)字(00……31)
%e 月份中的天數(shù), 數(shù)字(0……31)
%m 月, 數(shù)字(01……12)
%c 月, 數(shù)字(1……12)
%b 縮寫的月份名字(Jan……Dec)
%j 一年中的天數(shù)(001……366)
%H 小時(shí)(00……23)
%k 小時(shí)(0……23)
%h 小時(shí)(01……12)
%I 小時(shí)(01……12)
%l 小時(shí)(1……12)
%i 分鐘, 數(shù)字(00……59)
%r 時(shí)間,12 小時(shí)(hh:mm:ss [AP]M)
%T 時(shí)間,24 小時(shí)(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一個(gè)星期中的天數(shù)(0=Sunday ……6=Saturday ）
%U 星期(0……52), 這里星期天是星期的第一天
%u 星期(0……52), 這里星期一是星期的第一天
%% 一個(gè)文字“%”。

本文只是記錄在項(xiàng)目中用到的統(tǒng)計(jì)的SQL語句，記一筆以防忘了

 /// <summary>
 /// 獲取統(tǒng)計(jì)數(shù)據(jù)
 /// </summary>
 /// <param name="CKEY">店面ckey</param>
 /// <param name="type">統(tǒng)計(jì)類型（日、周、月、年）</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 日成交數(shù)量 , ");
 strSql.AppendFormat(" tbB.日實(shí)收金額 , ");
 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.日實(shí)收金額, 0) ");
 strSql.AppendFormat(" END AS 日實(shí)收金額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 日實(shí)收金額 , ");
 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 日成交數(shù)量 , ");
 strSql.AppendFormat(" tbB.日實(shí)收金額 , ");
 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.日實(shí)收金額, 0) ");
 strSql.AppendFormat(" END AS 日實(shí)收金額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 日實(shí)收金額 , ");
 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 月成交數(shù)量 , ");
 strSql.AppendFormat(" tb.月實(shí)收總金額 , ");
 strSql.AppendFormat(" CASE WHEN YearMonth.月 > MONTH(GETDATE()) THEN NULL ");
 strSql.AppendFormat(" WHEN YearMonth.月 <= MONTH(GETDATE()) THEN ISNULL(tb.月實(shí)收總金額, 0) ");
 strSql.AppendFormat(" END AS 月實(shí)收總金額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 月實(shí)收總金額 , ");
 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 年實(shí)收總金額 , ");
 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;
 }

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com