代码之家 › 专栏 › 技术社区 › MAK

查询以获取一对多和多对一员工项目关系

sql-server-2008-r2 sql-server

MAK · 技术社区 · 7 年前

我有下表:

create table emp_project_mapping 
(
    emp_id varchar(10),
    pro_id varchar(10)
)

记录:

insert into emp_project_mapping values('E101','P1'),
                                      ('E101','P2'),
                                      ('E102','P1'),
                                      ('E103','P3'),
                                      ('E104','P3'),
                                      ('E105','P4');

问题1 :查找正在处理多个项目的员工。

emp_id  pro_id
--------------
E101    P1
E101    P2

:查找在同一项目中工作的多个员工。

emp_id  pro_id
--------------
E103    P3
E104    P3  
E101    P1
E102    P1

问题1:

;WITH CTE
AS
(
    SELECT  emp_id,
            pro_id,
            DENSE_RANK() over(order by emp_id) empid_rank,
            DENSE_RANK() over(order by pro_id) proid_rank
    FROM emp_project_mapping
)
SELECT emp_id,pro_id
FROM CTE
WHERE empid_rank = 1 and proid_rank>1

输出:

emp_id  pro_id
---------------
E101    P2

问题2:

;WITH CTE
AS
(
    SELECT  emp_id,
            pro_id,
            DENSE_RANK() over(order by emp_id) empid_rank,
            DENSE_RANK() over(order by pro_id) proid_rank
    FROM emp_project_mapping
)
SELECT emp_id,pro_id
FROM CTE
WHERE empid_rank > 1 and proid_rank = 1

输出:

emp_id  pro_id
----------------
E102    P1

1 回复 | 直到 7 年前

DarkRob 7 年前

对于第一个解决方案

        ;WITH CTE
        AS
        (
            SELECT  emp_id,
                    pro_id,
                    ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY pro_id) empid_rank
            FROM emp_project_mapping
        )
        SELECT * from emp_project_mapping WHERE emp_id IN (SELECT emp_id
        FROM CTE
        WHERE empid_rank > 1 )

对于第二种解决方案

        ;WITH CTE
        AS
        (
            SELECT  emp_id,
                    pro_id,
                    ROW_NUMBER() OVER (PARTITION BY pro_id ORDER BY emp_id) proid_rank
            FROM emp_project_mapping
        )
        SELECT * FROM emp_project_mapping WHERE pro_id IN (SELECT pro_id 
        FROM CTE 
        WHERE proid_rank>1)

推荐文章

Community wiki · SqlConnections,Parallel.For,一个旧的C#应用程序,初始化SqlConnections时随机挂起

3 年前