select id=identity(int,1,1) , Name,Status into tb1 from tb where Status = '新增'
select id=identity(int,1,1) , Name,Status into tb2 from tb where Status = '删除'select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from tb1 full join fb2 on tb1.id = tb2.id
select id=identity(int,1,1) , Name,Status into tb2 from tb where Status = '删除'select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from tb1 full join fb2 on tb1.id = tb2.id
select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '新增' and name < a.name) + 1 FROM tb a) tb1
full join
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '删除' and name < a.name) + 1 FROM tb a) tb2
on on tb1.px = tb2.px
select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '新增' and name < a.name) + 1 FROM tb a) tb1
full join
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '删除' and name < a.name) + 1 FROM tb a) tb2
on tb1.px = tb2.px
insert into tb values(1, 'AAA', '新增')
insert into tb values(2, 'BBB', '新增')
insert into tb values(3, 'CCC', '删除')
insert into tb values(4, 'DDD', '新增')
go--1
select id=identity(int,1,1) , Name,Status into tb1 from tb where Status = '新增'
select id=identity(int,1,1) , Name,Status into tb2 from tb where Status = '删除'
select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from tb1 full join tb2 on tb1.id = tb2.id--2
select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '新增' and name < a.name) + 1 FROM tb a WHERE Status = '新增') tb1
full join
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '删除' and name < a.name) + 1 FROM tb a WHERE Status = '删除') tb2
on tb1.px = tb2.pxdrop table tb,tb1,tb2/*
name1 status1 name2 status2
---------- ---------- ---------- ----------
AAA 新增 CCC 删除
BBB 新增
DDD 新增 (所影响的行数为 3 行)
*/
--建議改為用id判斷
select isnull(tb1.name,'') name1 , isnull(tb1.status,'') status1,
isnull(tb2.name,'') name2 , isnull(tb2.status,'') status2
from
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '新增' and id < a.id) + 1 FROM tb a) tb1
full join
(SELECT * , px=(SELECT COUNT(name) FROM tb WHERE Status = '删除' and id < a.id) + 1 FROM tb a) tb2
on on tb1.px = tb2.px
INSERT @t1 SELECT 'AAA', '新增'
UNION ALL SELECT 'BBB' , '新增'
UNION ALL SELECT 'CCC' , '删除'
UNION ALL SELECT 'DDD' ,'新增' SELECT a.*,ISNULL(b.name,''),ISNULL(b.status,'')
FROM @t1 a
FULL JOIN @t1 b
ON (SELECT COUNT(*) FROM @t1 WHERE status=b.status AND id<=b.id)
=
(SELECT COUNT(*) FROM @t1 WHERE status=a.status AND id<=a.id)
AND b.status='删除'
WHERE a.status='新增'/*
1 AAA 新增 CCC 删除
2 BBB 新增
4 DDD 新增
*/