declare @ta table( id int, a int)
declare @tb table( id int, b int)insert into @ta select 1,1
union all select 1,2
union all select 1,3
union all select 2,12
union all select 2,15
union all select 3,5
union all select 3,9
insert into @tb select 1,5
union all select 1,6
union all select 2,11
union all select 2,13
union all select 2,14
union all select 3,5select * from @ta
/*
id a
1 1
1 2
1 3
2 12
2 15
3 5
3 9
*/
select * from @tb
/*
id b
1 5
1 6
2 11
2 13
2 14
3 5
*/--想要的結果
/*
id a b
1 1 5
1 2 6
1 3 null
2 null 11
2 12 13
2 15 14
3 5 5
3 9 null
*/最好不要用临时表(identity列)
declare @tb table( id int, b int)insert into @ta select 1,1
union all select 1,2
union all select 1,3
union all select 2,12
union all select 2,15
union all select 3,5
union all select 3,9
insert into @tb select 1,5
union all select 1,6
union all select 2,11
union all select 2,13
union all select 2,14
union all select 3,5select * from @ta
/*
id a
1 1
1 2
1 3
2 12
2 15
3 5
3 9
*/
select * from @tb
/*
id b
1 5
1 6
2 11
2 13
2 14
3 5
*/--想要的結果
/*
id a b
1 1 5
1 2 6
1 3 null
2 null 11
2 12 13
2 15 14
3 5 5
3 9 null
*/最好不要用临时表(identity列)
1 1 5
1 2 6
1 3 null
2 null 11 --这块的数据为什么是 null 11 呢?怎么得到
2 12 13
2 15 14
3 5 5
3 9 null
declare @tb table( id int, b int)insert into @ta select 1,1
union all select 1,2
union all select 1,3
union all select 2,12
union all select 2,15
union all select 3,5
union all select 3,9
insert into @tb select 1,5
union all select 1,6
union all select 2,11
union all select 2,13
union all select 2,14
union all select 3,5SELECT
ISNULL(A.id,B.id) AS id,
A.a,
B.b
FROM(
SELECT
id,a,
rid=(SELECT COUNT(*)
FROM @ta
WHERE t.id=id
AND a<=t.a)
FROM @ta AS t
) AS A
FULL JOIN(
SELECT
id,b,
rid=(SELECT COUNT(*)
FROM @tb
WHERE t.id=id
AND b<=t.b)
FROM @tb AS t
) AS B
ON A.ID=B.ID
AND A.rid=B.rid
ORDER BY id,A.a,B.b/*
id a b
----------- ----------- -----------
1 1 5
1 2 6
1 3 NULL
2 NULL 14
2 12 11
2 15 13
3 5 5
3 9 NULL(8 行受影响)
*/
/*
id a b
1 1 5
1 2 6
1 3 null
2 null 11
2 12 13
2 15 14
3 5 5
3 9 null
*/
如果是
--想要的結果
/*
id a b
1 1 5
1 2 6
1 3 null
2 12 11
2 15 13
2 null 14
3 5 5
3 9 null
*/
这样才合理.
declare @tb table( id int, b int)
insert into @ta select 1,1
union all select 1,2
union all select 1,3
union all select 2,12
union all select 2,15
union all select 3,5
union all select 3,9
insert into @tb select 1,5
union all select 1,6
union all select 2,11
union all select 2,13
union all select 2,14
union all select 3,5select isnull(m.id , n.id) id , m.a , n.b from
(select * , px = (select count(1) from @ta where id = t.id and a < t.a) + 1 from @ta t) m
full join
(select * , px = (select count(1) from @tb where id = t.id and b < t.b) + 1 from @tb t) n
on m.id = n.id and m.px = n.px/*
id a b
----------- ----------- -----------
1 1 5
1 2 6
2 12 11
2 15 13
2 NULL 14
3 5 5
1 3 NULL
3 9 NULL(所影响的行数为 8 行)*/
declare @tb table( id int, b int)
insert into @ta select 1,1
union all select 1,2
union all select 1,3
union all select 2,12
union all select 2,15
union all select 3,5
union all select 3,9
insert into @tb select 1,5
union all select 1,6
union all select 2,11
union all select 2,13
union all select 2,14
union all select 3,5select isnull(m.id , n.id) id , m.a , n.b from
(select * , px = (select count(1) from @ta where id = t.id and a < t.a) + 1 from @ta t) m
full join
(select * , px = (select count(1) from @tb where id = t.id and b < t.b) + 1 from @tb t) n
on m.id = n.id and m.px = n.px
order by id , a , b
/*
id a b
----------- ----------- -----------
1 1 5
1 2 6
1 3 NULL
2 NULL 14
2 12 11
2 15 13
3 5 5
3 9 NULL(所影响的行数为 8 行)
*/