SQL按照日、周、月、年统计数据的方法分享
数据库 / 管理员 发布于 5年前 578
--按日
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脚本中的注释引起的奇怪问题
下一条：
浅谈sqlserver的负载均衡问题
- 相关文章
- 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
- DevDB - 在 VS 代码中直接访问数据库(0个评论)
- 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
- 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
- php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
- 近期文章
- 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
- 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
- gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
- 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
- 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
- Laravel从Accel获得5700万美元A轮融资(0个评论)
- 在go + gin中gorm实现指定搜索/区间搜索分页列表功能接口实例(0个评论)
- 在go语言中实现IP/CIDR的ip和netmask互转及IP段形式互转及ip是否存在IP/CIDR(0个评论)
- PHP 8.4 Alpha 1现已发布！(0个评论)
- 近期评论
-
122 在
学历：一种延缓就业设计，生活需求下的权衡之选中评论 工作几年后，报名考研了，到现在还没认真学习备考，迷茫中。作为一名北漂互联网打工人.. -
123 在
Clash for Windows作者删库跑路了，github已404中评论 按理说只要你在国内，所有的流量进出都在监控范围内，不管你怎么隐藏也没用，想搞你分.. -
原梓番博客 在
在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了，今天刚看，已经添加。.. -
博主 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了，可以看看近期评论的其他文章.. -
1111 在
佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开，博主百忙中能否发个APP下载链接，佛跳墙或极光..
Copyright·© 2019 侯体宗版权所有·
粤ICP备20027696号
