SQL怎么按照日、周、月、年对数据进行统计
发表于：2024-11-18 作者：千家信息网编辑
千家信息网最后更新 2024年11月18日，本篇内容介绍了"SQL怎么按照日、周、月、年对数据进行统计"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所
千家信息网最后更新 2024年11月18日SQL怎么按照日、周、月、年对数据进行统计
本篇内容介绍了"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; }
"SQL怎么按照日、周、月、年对数据进行统计"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！
星期
小时
数字
统计
成交量
名字
天数
月份
金额
实收
数据
内容
字符
字符串
时间
更多
知识
缩写
语句
实用
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
群晖服务器管理器
mysql数据库解密
服务器出现press怎么办
和平区网络安全培训视频
国家最大的网络安全公司
王牌竞赛怎么看服务器
安卓版体态评估软件开发
电信 服务器发生异常
文件管理软件要服务器吗
北京复华网络技术有限公司
阿里提示服务器被暴力破解
用什么方法维护数据库表结构
社区团购app软件开发
原神 小米服务器
普通显卡可以插服务器吗
互联网云服务器设计
华为服务器初始密码
软件视频会议服务器配置
徐州小型软件开发
视频展示软件开发公司
黑龙江新一代网络技术服务设计
cbu软件开发
扣丁学堂数据库
和平区网络安全培训视频
常德java软件开发招聘
新华互联网科技发展的视频
互联网科技公司未来30年
数据库什么时候用decimal
政务网络安全管控意义
提高数据库的连接
