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

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


CREATE TABLE DEMO(ID INT,socre INT,type INT)

INSERT INTO DEMO
SELECT 1, 50, 1
UNION ALL
SELECT 1, 70, 5
UNION ALL
SELECT 1, 40, 17
UNION ALL
SELECT 1, 80, 17
UNION ALL
SELECT 2, 10, 7
UNION ALL
SELECT 2, 30, 14
UNION ALL
SELECT 3, 50, 9
UNION ALL
SELECT 3, 100, 17

SELECT * FROM DEMO

SELECT ID,SUM(socre)
FROM DEMO A
WHERE
    (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) =1
GROUP 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 all
select 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 all
select 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 tb
go
create table tb
(
 id int,
 score int,
 type int
)
go
insert into tb
select 1,50,1 union all
select 1,70,5 union all
select 1,40,17 union all
select 1,80,17 union all
select 2,10,7 union all
select 2,30,14 union all
select 3,50,9 union all
select 3,100,17
go
select id,score=sum(score) from (
select id,score from
(
 select id from tb group by id
) a
outer 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