帮忙，一sql有关问题

帮忙，一sql问题
这是一张表table1 
IDEmployeeName
15张三
17张三
7李四
8李四
23黎明
24黎明
25黎明
另外一张表 table2
EmployeeName
黎明
黎明
张三

结果
IDEmployeeName
15张三
24黎明
25黎明

意思就是在table1 中找出与table2 中名称相同的记录并且数量已table2为准




[解决办法]

SQL code

WITH CTE1 AS (SELECT ID,EmployeeName,ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY ID ) AS NFROM TABLE1) ,CTE2 AS (SELECT EmployeeName,ROW_NUMBER() OVER(PARTITION BY EmployeeName ORDER BY NEWID() ) AS NFROM TABLE2)SELECT A.ID,A.EmployeeName FROM CTE1 A,CTE2 BWHERE A.EmployeeName = B.EmployeeNameAND A.N = B.N
[解决办法]
SQL code
--> 测试数据：[table1]if object_id('[table1]') is not null drop table [table1]create table [table1]([ID] int,[EmployeeName] varchar(4))insert [table1]select 15,'张三' union allselect 17,'张三' union allselect 7,'李四' union allselect 8,'李四' union allselect 23,'黎明' union allselect 24,'黎明' union allselect 25,'黎明'--> 测试数据：[table2]if object_id('[table2]') is not null drop table [table2]create table [table2]([EmployeeName] varchar(4))insert [table2]select '黎明' union allselect '黎明' union allselect '张三';with tas(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 mon t.px=m.px and t.EmployeeName=m.EmployeeName/*ID    EmployeeName-------------------------24    黎明25    黎明17    张三*/--其实跟一楼是一样的