日期:2014-05-18  浏览次数:20639 次

帮忙写个sql!!!!
表信息如下
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 的记录分数总和

------解决方案--------------------
SQL code

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,11

if(exists (select 1 from @t where type=11))
select * from @t where type=11

if(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

------解决方案--------------------
这样?

SQL code
--> --> (Roy)生成測試數據
 
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]

------解决方案--------------------
SQL code

--楼主是要分组合计,还是按条件合计的
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 行受影响)

*/