有2个表,t_customer和t_address,表t_address中的cid是t_customer中id的外键,t_address中的num表示客户的地址数量,最多3个,数据如下:
id name
1 jason chen
2 kevin cid address num
1 shenzhen 1
1 nanjing 2
2 Tokyo 1
1 chengdu 3 要求SQL SERVER下写出性能最优的SQL查询出如下的结果:
id name addr1 addr2 addr3
1 jason chen shenzhen nanjing chengdu
2 kevin Tokyo NULL NULL
感激不尽啊!
id name
1 jason chen
2 kevin cid address num
1 shenzhen 1
1 nanjing 2
2 Tokyo 1
1 chengdu 3 要求SQL SERVER下写出性能最优的SQL查询出如下的结果:
id name addr1 addr2 addr3
1 jason chen shenzhen nanjing chengdu
2 kevin Tokyo NULL NULL
感激不尽啊!
select m.* ,
max(case px when 1 then n.address end) addr1,
max(case px when 2 then n.address end) addr2,
max(case px when 3 then n.address end) addr3
from t_customer m,
(
select t.* , px = (select count(1) from t_address from cid = t.cid and (address < t.address or (address = t.address and num < t.address))) + 1 from t_address t
) n
where m.id = n.cid
group by m.id,m.name
order by m.id--sql 2005
select m.* ,
max(case px when 1 then n.address end) addr1,
max(case px when 2 then n.address end) addr2,
max(case px when 3 then n.address end) addr3
from t_customer m,
(
select t.* , px = row_number() over(partition by cid order by address , num) from t_address t
) n
where m.id = n.cid
group by m.id,m.name
order by m.id
insert into t_customer values(1 ,'jason chen')
insert into t_customer values(2 ,'kevin')
create table t_address(cid int,address varchar(20),num int)
insert into t_address values(1 ,'shenzhen' ,1 )
insert into t_address values(1 ,'nanjing' ,2 )
insert into t_address values(2 ,'Tokyo' ,1 )
insert into t_address values(1 ,'chengdu' ,3 )
go--sql 2000
select m.* ,
max(case px when 1 then n.address end) addr1,
max(case px when 2 then n.address end) addr2,
max(case px when 3 then n.address end) addr3
from t_customer m,
(
select t.* , px = (select count(1) from t_address where cid = t.cid and (address < t.address or (address = t.address and num < t.num))) + 1 from t_address t
) n
where m.id = n.cid
group by m.id,m.name
order by m.id
/*
id name addr1 addr2 addr3
----------- -------------------- -------------------- -------------------- --------------------
1 jason chen chengdu nanjing shenzhen
2 kevin Tokyo NULL NULL(所影响的行数为 2 行)
*/--sql 2005
select m.* ,
max(case px when 1 then n.address end) addr1,
max(case px when 2 then n.address end) addr2,
max(case px when 3 then n.address end) addr3
from t_customer m,
(
select t.* , px = row_number() over(partition by cid order by address , num) from t_address t
) n
where m.id = n.cid
group by m.id,m.name
order by m.iddrop table t_customer,t_address