溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“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語句，記一筆以防忘了
/// <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); return rs; }“SQL怎么按照日、周、月、年對數據進行統計”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
