34,590
社区成员
发帖
与我相关
我的任务
分享
上午点击量 下午点击量 晚上点击量 上午消费 下午消费 晚上消费 状态
111 222 333 111.1 232.11 432.11 上午
222 777 333 222.22 43.1 321.33 下午
333 444 333 332.33 32.33 555 晚上
444 444 222 234.22 443.32 222.3 晚上
Create table #T([上午点击量] int,[下午点击量] int,[晚上点击量] int,[上午消费] decimal(18,2),[下午消费] decimal(18,2),[晚上消费] decimal(18,2),[状态] nvarchar(2))
Insert #T
select 111,222,333,111.1,232.11,432.11,N'上午' union all
select 222,777,333,222.22,43.1,321.33,N'下午' union all
select 333,444,333,332.33,32.33,555,N'晚上' union all
select 444,444,222,234.22,443.32,222.3,N'晚上'
select SUM(case
when [状态] = N'上午' then [上午点击量]
when [状态] = N'下午' then [下午点击量]
when [状态] = N'晚上' then [晚上点击量]
end
) 总点击量
, SUM(case
when [状态] = N'上午' then [上午消费]
when [状态] = N'下午' then [下午消费]
when [状态] = N'晚上' then [晚上消费]
end
) 总消费
from #T
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([上午点击量] int,[下午点击量] int,[晚上点击量] int,[上午消费] decimal(18,2),[下午消费] decimal(18,2),[晚上消费] decimal(18,2),[状态] nvarchar(2))
Insert #T
select 111,222,333,111.1,232.11,432.11,N'上午' union all
select 222,777,333,222.22,43.1,321.33,N'下午' union all
select 333,444,333,332.33,32.33,555,N'晚上' union all
select 444,444,222,234.22,443.32,222.3,N'晚上'
Go
select [上午点击量]+[下午点击量]+[晚上点击量] as 总点击,
[上午消费]+[下午消费]+[晚上消费] as 总消费
from
(Select SUM([上午点击量]) as [上午点击量],SUM([上午消费]) as [上午消费] from #T as a where [状态]=N'上午') as a,
(Select SUM([下午点击量]) as [下午点击量],SUM([下午消费]) as [下午消费] from #T as a where [状态]=N'下午') as b,
(Select SUM([晚上点击量]) as [晚上点击量],SUM([晚上消费]) as [晚上消费] from #T as a where [状态]=N'晚上') as c
/*
总点击 总消费
1443 931.50
*/