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 //获取所以支付类型