这是一张表table1
ID EmployeeName
15 张三
17 张三
7 李四
8 李四
23 黎明
24 黎明
25 黎明
另外一张表 table2
EmployeeName
黎明
黎明
张三结果
ID EmployeeName
15 张三
24 黎明
25 黎明意思就是在table1 中找出与table2 中名称相同的记录并且数量已table2为准
ID EmployeeName
15 张三
17 张三
7 李四
8 李四
23 黎明
24 黎明
25 黎明
另外一张表 table2
EmployeeName
黎明
黎明
张三结果
ID EmployeeName
15 张三
24 黎明
25 黎明意思就是在table1 中找出与table2 中名称相同的记录并且数量已table2为准
SELECT ID,EmployeeName
,ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY ID ) AS N
FROM TABLE1
) ,CTE2 AS (
SELECT EmployeeName
,ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY NEWID() ) AS N
FROM TABLE2
)
SELECT A.ID,A.EmployeeName FROM CTE1 A,CTE2 B
WHERE A.EmployeeName = B.EmployeeName
AND A.N = B.N
--> 测试数据:[table1]
if object_id('[table1]') is not null
drop table [table1]
create table [table1]([ID] int,[EmployeeName] varchar(4))
insert [table1]
select 15,'张三' union all
select 17,'张三' union all
select 7,'李四' union all
select 8,'李四' union all
select 23,'黎明' union all
select 24,'黎明' union all
select 25,'黎明'
--> 测试数据:[table2]
if object_id('[table2]') is not null
drop table [table2]
create table [table2]([EmployeeName] varchar(4))
insert [table2]
select '黎明' union all
select '黎明' union all
select '张三';with t
as(
select
px=ROW_NUMBER()over(partition by [EmployeeName] order by newid())
,* from [table1]
),
m as(
select
px=ROW_NUMBER()over(partition by [EmployeeName] order by newid())
,* from [table2]
)
select t.ID,m.EmployeeName
from t
inner join m
on t.px=m.px and t.EmployeeName=m.EmployeeName/*
ID EmployeeName
-------------------------
24 黎明
25 黎明
17 张三
*/
--其实跟一楼是一样的