上午点击量 下午点击量 晚上点击量 上午消费 下午消费 晚上消费 状态
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 晚上
当状态为“上午”时,点击量的汇总求和时 取“上午点击量”列的值
当状态为“下午”时,点击量的汇总求和 时 取的是“下午点击量“列的值
当状态为“晚上”时,点击量汇总求和 时 取的是 "晚上点击量"列的值
既点击量 汇总求和的值应该为 111+777+333+222=1443同理,消费的汇总求和 应该为 111.1 +43.1+555 +222.3 =931.5最终想查询出来的效果是两列,分别是 【总点击】 和【总消费】,如下所示
总点击 总消费
1443 931.5
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
*/
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