27,579
社区成员
发帖
与我相关
我的任务
分享
drop table if exists testTable1;
create table testTable1( id INT PRIMARY KEY,[name] NVARCHAR(20), parentId INT );
INSERT INTO testTable1(id,[name],parentId) VALUES(2,'xf1',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(3,'xf2',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(5,'xf3',2);
INSERT INTO testTable1(id,[name],parentId) VALUES(6,'xf4',3);
INSERT INTO testTable1(id,[name],parentId) VALUES(7,'xf5',4);
INSERT INTO testTable1(id,[name],parentId) VALUES(9,'xf6',5);
delete from testTable1 where id=7;
select
rowid
,(select count(*) from testTable1 b where a.id >= b.id) as row_number
,*
from testTable1 as a
order by id;
/*
rowid row_number id name parentId
1 1 2 xf1 0
2 2 3 xf2 0
3 3 5 xf3 2
4 4 6 xf4 3
6 5 9 xf6 5
*/
create table if not exists testTable1( id INT PRIMARY KEY,[name] NVARCHAR(20), parentId INT );
INSERT INTO testTable1(id,[name],parentId) VALUES(2,'xf1',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(3,'xf2',0);
INSERT INTO testTable1(id,[name],parentId) VALUES(5,'xf3',2);
INSERT INTO testTable1(id,[name],parentId) VALUES(6,'xf4',3);
INSERT INTO testTable1(id,[name],parentId) VALUES(7,'xf5',4);
INSERT INTO testTable1(id,[name],parentId) VALUES(9,'xf6',5);
select rowid as RowNumber ,* from testTable1 order by id
use Tempdb
go
--> --> 听雨停了-->测试数据
if not object_id(N'Tempdb..#tabA') is null
drop table #tabA
Go
Create table #tabA([A] int,[B] int,[C] int,[D] int,[E] int)
Insert #tabA
select 1,2,3,4,5 union all
select 8,2,3,4,5 union all
select 1,1,1,1,1 union all
select 1,null,1,2,3
if not object_id(N'Tempdb..#tabB') is null
drop table #tabB
Go
Create table #tabB([B] int,[C] int,[D] int)
Insert #tabB
select 1,1,1
--测试数据结束
--1.除去BCD重复且B不等于null的数据,插入到#tabC中
SELECT DISTINCT B,C,D
INTO #tabC
FROM #tabA
WHERE ISNULL(B,'')<>''
--2.使用merge函数进行数据同步
MERGE INTO #tabB AS T
USING #tabC AS S
ON t.B=s.B AND t.C=s.C AND t.D=s.D
WHEN NOT matched --目标表(#tabB)中没有时,源表(#tabC)数据插入
THEN INSERT values(s.B,s.C,s.D);
SELECT * FROM #tabB
B C D
----------- ----------- -----------
1 1 1
2 3 4
--测试数据
if not object_id(N'Tempdb..#tableA') is null
drop table #tableA
Go
Create table #tableA([A] int,[B] int,[C] int,[D] int,[E] int)
Insert #tableA
select 1,1,1,1,1 union ALL
select 1,1,1,1,1 union all
select 2,NULL,2,2,2 union all
select 3,3,3,3,3
GO
if not object_id(N'Tempdb..#tableB') is null
drop table #tableB
Go
Create table #tableB([B] int,[C] int,[D] int)
Insert #tableB
select 1,2,3
Go
--测试数据结束
INSERT INTO #tableB
( B, C, D )
SELECT DISTINCT
B ,
C ,
D
FROM #tableA
WHERE B IS NOT NULL
SELECT * FROM #tableB