联表查询....

表信息如下:
id socre type
-----------------------
1 50 1
1 70 5
1 40 17
1 80 17
2 10 7
2 30 14
3 50 9
3 100 17

条件: 存在type=17的 则取type=17的sum(socre),
  存在type=(13~16)的 则取type=(13~16)的sum(socre),
  存在type=(1~12)的 则取type=(1~12)的sum(socre),
例如: id=1 的 存在type = 17 则结果如下
id socre type
-----------------------
1 120 17

例如: id=2 的 不存在type = 17 则判断是否存在type=(13~16), 则结果应如下
id socre type
-------------------------
2 30 14

我想要的最终结果 如下
id socre type
------------------------------
1 120 17
2 30 14
3 100 17

最后结果中 也可以不要[type]那一列, 有id和 socre 即可

SQL code

create table tb(id int,socre int,type int)
insert into tb select 1,50,1
insert into tb select 1,70,5
insert into tb select 1,40,17
insert into tb select 1,80,17
insert into tb select 2,10,7
insert into tb select 2,30,14
insert into tb select 3,50,9
insert into tb select 3,100,17
go
select id,sum(socre)socre,17 as type 
from tb a
where exists(select 1 from tb where id=a.id and type=17) and type=17
group by id
union all
select id,SUM(socre),MAX(type) 
from tb a 
where not exists(select 1 from tb where id=a.id and type=17) and type between 13 and 16
group by id
union all
select ID,SUM(socre),MAX(type)
from tb a
where not exists(select 1 from tb where id=a.id and type>12)
group by id
/*
id          socre       type
----------- ----------- -----------
1           120         17
3           100         17
2           30          14

(3 行受影响)

*/
go
drop table tb