联表查询.该怎么处理

联表查询....
表信息如下:
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

[解决办法]

SQL code

create table tb(id int,socre int,type int)insert into tb select 1,50,1insert into tb select 1,70,5insert into tb select 1,40,17insert into tb select 1,80,17insert into tb select 2,10,7insert into tb select 2,30,14insert into tb select 3,50,9insert into tb select 3,100,17goselect id,sum(socre)socre,17 as type from tb awhere exists(select 1 from tb where id=a.id and type=17) and type=17group by idunion allselect 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 16group by idunion allselect ID,SUM(socre),MAX(type)from tb awhere not exists(select 1 from tb where id=a.id and type>12)group by id/*id          socre       type----------- ----------- -----------1           120         173           100         172           30          14(3 行受影响)*/godrop table tb
[解决办法]
SQL code
CREATE TABLE DEMO(ID INT,socre INT,type INT)INSERT INTO DEMOSELECT 1, 50, 1UNION ALLSELECT 1, 70, 5UNION ALLSELECT 1, 40, 17UNION ALLSELECT 1, 80, 17UNION ALLSELECT 2, 10, 7UNION ALLSELECT 2, 30, 14UNION ALLSELECT 3, 50, 9UNION ALLSELECT 3, 100, 17SELECT * FROM DEMOSELECT ID,SUM(socre)FROM DEMO AWHERE    (CASE WHEN ((SELECT MAX(TYPE) FROM DEMO WHERE ID=A.ID)=17 AND TYPE=17) OR     (((SELECT MAX(TYPE) FROM DEMO WHERE ID=A.ID) BETWEEN 13 AND 16) AND TYPE BETWEEN 13 AND 16) OR     (((SELECT MAX(TYPE) FROM DEMO WHERE ID=A.ID) BETWEEN 1 AND 12) AND TYPE BETWEEN 1 AND 12) THEN 1     ELSE 0      END) =1GROUP BY ID
[解决办法]
SQL code
select ID,SUM(case when type=17 then socre else 0 end) from tb where type=17 group by id union allselect ID,SUM(case when type between 13 and 16 then socre else 0 end) from tb where type between 13 and 16  group by id union allselect ID,SUM(case when type between 1 and 12 then socre else 0 end) from tb where type between 1 and 12  group by id
[解决办法]
SQL code
if object_id('tb') is not null   drop table tbgocreate table tb( id int, score int, type int)goinsert into tbselect 1,50,1 union allselect 1,70,5 union allselect 1,40,17 union allselect 1,80,17 union allselect 2,10,7 union allselect 2,30,14 union allselect 3,50,9 union allselect 3,100,17goselect id,score=sum(score) from (select id,score from( select id from tb group by id) aouter apply (  select score=      case when exists(select 1 from tb where id=a.id and type=17) then (case when type=17 then score else 0 end)           when exists(select 1 from tb where id=a.id and type between 13 and 16) then (case when type between 13 and 16 then score else 0 end)           when exists(select 1 from tb where id=a.id and type between 1 and 12) then (case when type between 1 and 12 then score else 0 end)      end  from tb where id=a.id)b) c group by id/*id          score----------- -----------1           1202           303           100(3 行受影响)*/