SQL按照日、周、月、年统计数据的方法分享
作者：mdxy-dxy 时间：2024-01-24 13:48:27
--按日
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;
}
标签：统计数据
0
投稿猜你喜欢
Golang如何实现任意进制转换的方法示例
2024-02-23 07:43:48
Python练习之操作MySQL数据库
2024-01-20 12:29:22
mysql5.7 修改用户初始密码的方法
2024-01-14 17:33:36
python数字图像处理之骨架提取与分水岭算法
2023-03-07 15:59:50
Javascript 回调和事件（翻译）
2009-03-28 11:47:00
使用ERWin进行基于MySQL数据库的物理设计
2009-01-04 12:54:00
python批量处理txt文件的实例代码
2023-05-26 07:00:33
网页设计之关于素材和言志
2008-03-23 13:46:00
SQL Server如何插入数据示例代码
2024-01-13 16:42:33
如何利用Python实现一个论文降重工具
2021-02-04 08:11:28
Python可变参数用法实例分析
2022-01-20 04:24:55
php实现用于计算执行时间的类实例
2024-06-07 15:28:42
python获取各操作系统硬件信息的方法
2021-03-29 17:09:05
iis7 ASP+Access数据库连接错误
2011-03-08 10:41:00
Perl split字符串分割函数用法指南
2023-08-13 01:28:36
Python 中的with关键字使用详解
2022-10-03 08:25:25
Vue SPA 初次进入加载动画实现代码
2023-07-02 16:56:25
Python手机与电脑游戏脚本的编写方法
2023-11-28 18:31:44
总结Python编程中三条常用的技巧
2022-06-04 05:52:04
go语言中函数与方法介绍
2024-04-23 09:34:13