id tp1 a1 tp2 a2
1 1 30 null null
1 2 40 null null
1 null null 1 40
1 null null 2 50
1 3 60 null null
1 null null 4 70转换成下面的结果id tp1 a1 tp2 a21 1 30 1 40
1 2 40 2 50
1 3 60 null null
1 null null 4 70即就是tp1与tp2相等的合成一列,不符合的另附一行。创建表如下:if object_id('[testtb]') is not null drop table [testtb]create table testtb(id int,tp1 varchar(10),a1 varchar(10),tp2 varchar(10),a2 varchar(10))insert into testtb
select 1,'1','30',null,null union all
select 1,'2','40',null,null union all
select 1,null,null,'1','40' union all
select 1,null,null,'2','50' union all
select 1,'3','60',null,null union all
select 1,null,null,'4','70'
select * from testtb1 1 30 null null
1 2 40 null null
1 null null 1 40
1 null null 2 50
1 3 60 null null
1 null null 4 70
1 1 30 null null
1 2 40 null null
1 null null 1 40
1 null null 2 50
1 3 60 null null
1 null null 4 70转换成下面的结果id tp1 a1 tp2 a21 1 30 1 40
1 2 40 2 50
1 3 60 null null
1 null null 4 70即就是tp1与tp2相等的合成一列,不符合的另附一行。创建表如下:if object_id('[testtb]') is not null drop table [testtb]create table testtb(id int,tp1 varchar(10),a1 varchar(10),tp2 varchar(10),a2 varchar(10))insert into testtb
select 1,'1','30',null,null union all
select 1,'2','40',null,null union all
select 1,null,null,'1','40' union all
select 1,null,null,'2','50' union all
select 1,'3','60',null,null union all
select 1,null,null,'4','70'
select * from testtb1 1 30 null null
1 2 40 null null
1 null null 1 40
1 null null 2 50
1 3 60 null null
1 null null 4 70
select a.id,a.tp1,a.a1,b.tp2,a.a2 from testtb a LEFT JOIN testtb b ON
a.tp1=b.tp2
/*
id tp1 a1 tp2 a2
----------- ---------- ---------- ---------- ----------
1 1 30 1 NULL
1 2 40 2 NULL
1 NULL NULL NULL 40
1 NULL NULL NULL 50
1 3 60 NULL NULL
1 NULL NULL NULL 70
*/
SELECT a.id ,
a.tp1 ,
a.a1 ,
ISNULL(b.tp2, a.tp2) AS tp2 ,
ISNULL(b.a2, a.a2) AS a2
FROM testtb a
LEFT JOIN testtb b ON a.tp1 = b.tp2
WHERE
/*
id tp1 a1 tp2 a2
----------- ---------- ---------- ---------- ----------
1 1 30 1 40
1 2 40 2 50
1 NULL NULL 1 40
1 NULL NULL 2 50
1 3 60 NULL NULL
1 NULL NULL 4 70
*/楼主看看是不是这个意思,第3行,第4行因为什么去掉的?
(
SELECT a.id ,
a.tp1 ,
a.a1 ,
ISNULL(b.tp2, a.tp2) AS tp2 ,
ISNULL(b.a2, a.a2) AS a2
FROM testtb a
LEFT JOIN testtb b ON a.tp1 = b.tp2
) aa GROUP BY aa.tp2,aa.a2
ORDER BY ISNULL(MIN(tp1),9999)/*
id tp1 a1 tp2 a2
----------- ---------- ---------- ---------- ----------
1 1 30 1 40
1 2 40 2 50
1 3 60 NULL NULL
1 NULL NULL 4 70
*/--总感觉逻辑怪怪的。
按哪列排都不行,正序倒序我都没搞定,
只好添加isnull(min(tp1),9999)了。