B2C经典查询,统计 绝对用的上,根据日期分组统计当天各种支付方式的销售数量和销售额

declare @sql varchar(8000)
set @sql = 'select CONVERT(varchar(10), OrderDate, 120) as 日期'
select @sql = @sql + ' , sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then OrderAmount else 0 end)[' + cast(Pwy2 as varchar) + ']
,sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then 1 else 0 end)[' + cast(Pwy2 as varchar) + '销量]'

from (select distinct id as Pwy,PayWay as Pwy2 from dbo.A_OrderPayWay) as a
set @sql = @sql + ',sum( case OrderAmount when NULL then 0 else OrderAmount end ) as 合计,count(*) as 总订单   from A_OrderInfo group by
CONVERT(varchar(10), OrderDate, 120) Order by CONVERT(varchar(10), OrderDate, 120) desc'
exec(@sql)

备注

[' + cast(Pwy2 as varchar) + '销量] //列名转换

CONVERT(varchar(10), OrderDate, 120)    //获取订单日期的 年月日部分 

sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then OrderAmount else 0 end)  //统计每种支付方式的当天金额

sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then 1 else 0 end)  //统计每种支付方式的当天销售数量

select distinct id as Pwy from dbo.A_OrderPayWay  //获取所以支付类型



B2C经典查询,统计 绝对用的上,根据日期分组统计当天各种支付方式的销售数量和销售额

上一篇:经典算法详解(10)图中有多少个三角形


下一篇:mysql连接数太小,导致网站报错mysql:Too many connections