SQL按照日、周、月、年统计数据的方法分享
程序员文章站
2023-12-04 23:23:22
--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006'...
--按日
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下三种批量插入数据的方法