表信息如下
id socre type
----------------------------------
1 50 1
1 40 1
1 30 2
1 40 2
1 70 11
1 60 11条件 : 如果表中存在tpye = 11 的则取 tpye = 11 的记录分数总和
如果表中不存在 tpye = 11 则取 type = 2 的记录分数总和
如果表中不存在 type = 2 则取 type = 1 的记录分数总和
id socre type
----------------------------------
1 50 1
1 40 1
1 30 2
1 40 2
1 70 11
1 60 11条件 : 如果表中存在tpye = 11 的则取 tpye = 11 的记录分数总和
如果表中不存在 tpye = 11 则取 type = 2 的记录分数总和
如果表中不存在 type = 2 则取 type = 1 的记录分数总和
declare @t table (id int,socre int,type int)
insert into @t
select 1,50,1 union all
select 1,40,1 union all
select 1,30,2 union all
select 1,40,2 union all
select 1,70,11 union all
select 1,60,11if(exists (select 1 from @t where type=11))
select * from @t where type=11if(not exists (select 1 from @t where type=11))
begin
if(not exists (select 1 from @t where type=2))
select * from @t where type=1
else
select * from @t where type=2
end
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[socre] int,[type] int)
Insert #T
select 1,50,1 union all
select 1,40,1 union all
select 1,30,2 union all
select 1,40,2 union all
select 1,70,11 union all
select 1,60,11
Go
--如果表中存在tpye = 11 的则取 tpye = 11 的记录分数总和
-- 如果表中不存在 tpye = 11 则取 type = 2 的记录分数总和
-- 如果表中不存在 type = 2 则取 type = 1 的记录分数总和
Select [type],[socre]=SUM([socre]) from #T where [type]=11 group by [type]
union all
Select [type],[socre]=SUM([socre]) from #T where not exists(select 1 from #T where [type]=11) and [type]=2 group by [type]
union all
Select [type],[socre]=SUM([socre]) from #T where not exists(select 1 from #T where [type] in(2,11)) and [type]=1 group by [type]
--楼主是要分组合计,还是按条件合计的
if object_id('tb') is not null
drop table tb
go
create table tb
(
id int,
socre int,
type int
)
go
insert into tb
select 1,50,1 union all
select 1,40,1 union all
select 1,30,2 union all
select 1,40,2 union all
select 1,70,11 union all
select 1,60,11
go
if exists(select 1 from tb where type=11)
select sum(socre) from tb where type=11
else if exists(select 1 from tb where type=2)
select sum(socre) from tb where type=2
else if exists(select 1 from tb where type=1)
select sum(socre) from tb where type=1
go
/*
-----------
130(1 行受影响)*/