不同记录的计数 - SQL

发布于 2024-12-08 04:14:08 字数 493 浏览 1 评论 0原文

empid   projectId   TaskID
100     500           1
100     501           1
100     502           1
101     500           2
101     500          5
101     500          1
102     400          1
103     300          2
104     300          2
105     300          2

我试图根据项目 id 列出仅从事多个项目的员工。我尝试了 unique 和 GROUP BY 。但我无法准确地计算出来。

从上表中我期待这样的结果

 empid   projectId  
    100     500         
    100     501          
    100     502

原文

empid   projectId   TaskID
100     500           1
100     501           1
100     502           1
101     500           2
101     500          5
101     500          1
102     400          1
103     300          2
104     300          2
105     300          2

I am trying to list the employees who works on multiple project only, based on project id .
I tried distinct and GROUP BY . but am not able figure it exactly.

from the above table am expecting a result like this

 empid   projectId  
    100     500         
    100     501          
    100     502

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

内心激荡 2024-12-15 04:14:08

试试这个（修订后的代码）

SELECT DISTINCT EmpId, ProjectId
FROM TableX
WHERE EmpId IN 
(
    SELECT EmpId
    FROM TableX
    GROUP BY EmpId
    HAVING COUNT (DISTINCT ProjectId) > 1
)

这应该会给你

EmpId       ProjectId
----------- -----------
100         500
100         501
100         502

3 row(s)

编辑为OPs添加的内容在评论中附加问题

给出你不同的ProjectIds的计数将意味着GROUP BY将处于EmpId 级别且无需子查询

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId

要获取具有多个项目的所有员工的项目计数，请执行以下操作

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId
Having Count (Distinct ProjectId) > 1

Try this (revised code)

SELECT DISTINCT EmpId, ProjectId
FROM TableX
WHERE EmpId IN 
(
    SELECT EmpId
    FROM TableX
    GROUP BY EmpId
    HAVING COUNT (DISTINCT ProjectId) > 1
)

This should give you

EmpId       ProjectId
----------- -----------
100         500
100         501
100         502

3 row(s)

Edit Content added for OPs additional question in the comments

A count giving you distint ProjectIds would mean that the GROUP BY would be at an EmpId level and no need for a subquery

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId

To get a count of projects for all employees with multiple projects, do the following

SELECT EmpId, Count (Distinct ProjectId) Projects
FROM TableX
GROUP BY EmpId
Having Count (Distinct ProjectId) > 1

回复收藏 0 原文

从﹋此江山别 2024-12-15 04:14:08

您还可以使用窗口 COUNT()：

WITH counted AS (
  SELECT
    empid,
    projectId,
    COUNT(DISTINCT projectId) OVER (PARTITION BY empid) AS ProjectCount
  FROM atable
)
SELECT DISTINCT
  empid,
  projectId
FROM counted
WHERE ProjectCount > 1

参考资料：

You could also use a windowed COUNT():

WITH counted AS (
  SELECT
    empid,
    projectId,
    COUNT(DISTINCT projectId) OVER (PARTITION BY empid) AS ProjectCount
  FROM atable
)
SELECT DISTINCT
  empid,
  projectId
FROM counted
WHERE ProjectCount > 1

References:

回复收藏 0 原文

情话难免假 2024-12-15 04:14:08

SELECT y.empid, y.projectId
    FROM (SELECT empid
              FROM YourTable
              GROUP BY empid
              HAVING COUNT(*) > 1) t
        INNER JOIN YourTable y
            ON t.empid = y.empid
    ORDER BY y.empid, y.projectId

SELECT y.empid, y.projectId
    FROM (SELECT empid
              FROM YourTable
              GROUP BY empid
              HAVING COUNT(*) > 1) t
        INNER JOIN YourTable y
            ON t.empid = y.empid
    ORDER BY y.empid, y.projectId

回复收藏 0 原文

~没有更多了~