mssql 统计

这篇文章主要为大家按日，星期，月，季度，年统计销售额的sql语句，需要的朋友可以参考下

 

--按日，统计本月数据
select sum(payable_amount) as 金额,day([payment_time]) as 日 from dt_orders where DateDiff(mm,[payment_time],getdate())=0 group by day([payment_time]) order by 日

--按周，统计本月数据
select sum(payable_amount) as 金额,datename(week,[payment_time]) as 周 from dt_orders where DateDiff(mm,[payment_time],getdate())=0 group by datename(week,[payment_time]) order by 周

--按月，统计本年数据
select sum(payable_amount) as 金额,month([payment_time]) as 月 from dt_orders where DateDiff(yy,[payment_time],getdate())=0 group by month([payment_time]) order by 月

--按季，统计本年数据
select sum(payable_amount) as 金额,datename(quarter,[payment_time]) as 季 from dt_orders where DateDiff(yy,[payment_time],getdate())=0 group by datename(quarter,[payment_time]) order by 季

--按年，统计所有数据
select sum(payable_amount) as 金额,year([payment_time]) as 年 from dt_orders where DateDiff(yy,[payment_time],getdate())=0 group by year([payment_time]) order by 年

 其他资料参考

--今天的所有数据
select * from dt_orders where DateDiff(dd,[payment_time],getdate())=0

--昨天的所有数据
select * from dt_orders where DateDiff(dd,[payment_time],getdate())=1

--7天内的所有数据
select * from dt_orders where DateDiff(dd,[payment_time],getdate())<=7

--30天内的所有数据
select * from dt_orders where DateDiff(dd,[payment_time],getdate())<=30