22,300
社区成员




DELETE FROM A2 WHERE A2.DM != '001'
INSERT into A2 (ID,DM,bm)
SELECT ROW_NUMBER() OVER (ORDER BY A1.DM) AS ID,
A1.DM,
a2.bm
FROM (SELECT * from A2 WHERE A2.DM ='001') as a2 ,A1 a1 WHERE a1.DM != a2.DM
SELECT * FROM A2 a2 ORDER BY a2.DM , a2.ID
--测试数据
if not object_id(N'Tempdb..#A1') is null
drop table #A1
Go
Create table #A1([DM] nvarchar(23),[mc] nvarchar(22))
Insert #A1
select N'001',N'北京' union all
select N'002',N'南京' union all
select N'003',N'上海'
GO
if not object_id(N'Tempdb..#A2') is null
drop table #A2
Go
Create table #A2([ID] int,[dm] nvarchar(23),[bm] nvarchar(21))
Insert #A2
select 1,N'001',N'A' union all
select 2,N'001',N'B' union all
select 3,N'001',N'C'
Go
--测试数据结束
SELECT ROW_NUMBER() OVER (ORDER BY #A1.DM) AS ID,
#A1.DM,
bm
FROM #A1,
#A2;
USE tempdb
GO
IF OBJECT_ID('A1') IS NOT NULL DROP TABLE A1
IF OBJECT_ID('A2') IS NOT NULL DROP TABLE A2
GO
CREATE TABLE A1(DM VARCHAR(10),mc NVARCHAR(10))
CREATE TABLE A2(ID INT,DM VARCHAR(10),bm NVARCHAR(10))
GO
INSERT INTO A1 VALUES ('001','北京')
INSERT INTO A1 VALUES ('002','南京')
INSERT INTO A1 VALUES ('003','上海')
GO
INSERT INTO A2 VALUES (1, '001','A')
INSERT INTO A2 VALUES (2, '001','B')
INSERT INTO A2 VALUES (3, '001','C')
GO
SELECT a2.id,a1.dm,a2.bm
FROM A1 CROSS JOIN A2 AS a2
ORDER BY a1.dm,a2.id
/*
id dm bm
1 001 A
2 001 B
3 001 C
1 002 A
2 002 B
3 002 C
1 003 A
2 003 B
3 003 C
*/