选择部门组

作者: 妖邪
发布时间: 2024-12-21 07:16:14 (1天前)
转自：

3 条回复

0#
回复此人
易米烊光 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 这是一个选项： </p> <pre> <code> SQL> with test (emp_id, dept) as 2 (select 100017, 'FIN' from dual union all 3 select 100017, 'HR' from dual union all 4 select 100017, 'ADMIN' from dual union all 5 select 100994, 'HR' from dual union all 6 select 100997, 'ADMIN' from dual union all 7 select 100997, 'FIN' from dual union all 8 select 100998, 'HR' from dual union all 9 select 100999, 'FIN' from dual 10 ) 11 select emp_id 12 from test 13 group by emp_id 14 having min(dept) = max(dept) 15 and min(dept) = 'HR'; EMP_ID ---------- 100998 100994 SQL> </code> </pre> </DIV>

编辑
1#
回复此人
无语 | 2019-08-31 10-32

<div class =“post-text”itemprop =“text”> <P> 我建议使用子查询来计算记录。如果特定id的记录计数大于1，则跳过id。 </p> <pre> <code> SELECT `emp_id`, `dept` FROM ( SELECT SUM(1) AS `count`, `emp_id`, `dept` FROM `table1` GROUP BY `emp_id` ) AS `derived` WHERE `count` = 1 AND `dept` = 'HR' </code> </pre> </DIV>

编辑

登录后才能参与评论