Oracle 查询：获取计数大于阈值的不同名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19826152/
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
Oracle Query: Get distinct names having count greater than a threshold
提问by CamelCase
I have a table having schema given below
我有一个表具有下面给出的架构
EmpID,MachineID,Timestamp
1, A,01-Nov-13
2, A,02-Nov-13
3, C,03-Nov-13
1, B,02-Nov-13
1, C,04-Nov-13
2, B,03-Nov-13
3, A,02-Nov-13
Desired Output:
期望输出：
EmpID,MachineID
1, A
1, B
1, C
2, A
2, B
3, A
3, C
So basically, I want to find the Emp who have used more than one machines in the given time period.
所以基本上，我想找到在给定时间段内使用过一台以上机器的 Emp。
The query I am using is
我正在使用的查询是
select EmpID,count(distinct(MachineID)) from table
where Timestamp between '01-NOV-13' AND '07-NOV-13'
group by EmpID having count(distinct(MachineID)) > 1
order by count(distinct(MachineID)) desc;
This query is given me output like this
这个查询给了我这样的输出
EmpID,count(distinct(MachineID))
1, 3
2, 2
3, 2
Can anyone help with making changes to get the output like described above in my question.
任何人都可以帮助进行更改以获得我的问题中如上所述的输出。
回答by Przemyslaw Kruglej
One possible solution:
一种可能的解决方案：
CREATE TABLE emp_mach (
empid NUMBER,
machineid VARCHAR2(1),
timestamp_val DATE
);
INSERT INTO emp_mach VALUES (1,'A', DATE '2013-11-01');
INSERT INTO emp_mach VALUES (2,'A', DATE '2013-11-02');
INSERT INTO emp_mach VALUES (3,'C', DATE '2013-11-03');
INSERT INTO emp_mach VALUES (1,'B', DATE '2013-11-02');
INSERT INTO emp_mach VALUES (1,'C', DATE '2013-11-04');
INSERT INTO emp_mach VALUES (2,'B', DATE '2013-11-03');
INSERT INTO emp_mach VALUES (3,'A', DATE '2013-11-02');
COMMIT;
SELECT DISTINCT empid, machineid
FROM emp_mach
WHERE empid IN (
SELECT empid
FROM emp_mach
WHERE timestamp_val BETWEEN DATE '2013-11-01' AND DATE '2013-11-07'
GROUP BY empid
HAVING COUNT(DISTINCT machineid) > 1
)
ORDER BY empid, machineid;
(I've changed the name of the timestampcolumn to timestamp_val)
（我已将timestamp列的名称更改为timestamp_val）
Output:
输出：
EMPID MACHINEID
---------- ---------
1 A
1 B
1 C
2 A
2 B
3 A
3 C
回答by Sebas
you did the hardest. Your query has to be used to filter out the results:
你做的最难。您必须使用查询来过滤结果：
SELECT t1.empid, t1.machineid
FROM
table t1
WHERE
EXIST (
SELECT
empid
FROM table t2
WHERE
timestamp BETWEEN '01-NOV-13' AND '07-NOV-13'
AND t2.empid = t1.empid
GROUP BY empid HAVING COUNT(distinct(machineid)) > 1
)
ORDER BY empid, machineid;
edit: posted a few secs after Przemyslaw Kruglej. I'll leave it here since it is just another alternative (using EXISTinstead of IN)
编辑：在 Przemyslaw Kruglej 几秒钟后发布。我将把它留在这里，因为它只是另一种选择（使用EXIST而不是IN）
回答by user206168
SELECT * FROM
(SELECT DISTINCT(EmpID),COUNT(*) AS NumEMP
from TableA
WHERE Timestamp between '01-NOV-13' AND '07-NOV-13'
group by EmpID
order by EmpID
)
WHERE NumEmp >= 1
