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  //擷取是以支付類型