# 联表查询.该怎么处理

www.myexceptions.net  网友分享于：2013-03-06  浏览：10次

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=(13~16)的 则取type=(13~16)的sum(socre),
存在type=(1~12)的 则取type=(1~12)的sum(socre),

id socre type
-----------------------
1 120 17

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,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 codeselect 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 id=a.id
)b
) c group by id
/*
id          score
----------- -----------
1           120
2           30
3           100

(3 行受影响)
*/```