不同记录的计数 - SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7638054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count of distinct records - SQL
提问by zod
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.
我试图根据项目 id 列出仅在多个项目上工作的员工。我尝试了 distinct 和 GROUP BY 。但我无法准确理解。
from the above table am expecting a result like this
从上表我期待这样的结果
empid projectId
100 500
100 501
100 502
采纳答案by Raj More
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)
EditContent added for OPs additional question in the comments
在评论中为 OP 附加问题添加了编辑内容
A count giving you distint ProjectIds would mean that the GROUP BY would be at an EmpIdlevel and no need for a subquery
为您提供 distint ProjectIds 的计数意味着 GROUP BY 将处于一个EmpId级别并且不需要子查询
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
回答by Andriy M
You could also use a windowed COUNT():
你也可以使用一个窗口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:
参考：
回答by Joe Stefanelli
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
