oracle 需要查询以显示同时拥有男性和女性员工的部门

yruzcnhs  于 2023-03-22  发布在  Oracle
关注(0)|答案(3)|浏览(203)

需要查询以显示同时拥有男性和女性员工的部门

CREATE TABLE departments(deptname VARCHAr2(20),sex VARCHAR2(2));

INSERT INTO departments VALUES('SALES','M');
INSERT INTO departments VALUES('SALES','F');
INSERT INTO departments VALUES('SALES','M');
INSERT INTO departments VALUES('SALES','F');
INSERT INTO departments VALUES('ENG','M');
INSERT INTO departments VALUES('ENG','F');
INSERT INTO departments VALUES('MKT','M');
INSERT INTO departments VALUES('CLE','F');
INSERT INTO departments VALUES('AUTO','M');
INSERT INTO departments VALUES('AUTO','M');
INSERT INTO departments VALUES('ENV','F');
INSERT INTO departments VALUES('ENV','F');

Expected Output
Deptname   Sex
SALES      M
SALES      F
SALES      M
SALES      F
ENG        M
ENG        F

请帮帮我。
先谢了。

jutyujz0

jutyujz01#

或者使用窗口函数来避免两次击中table:

SELECT *
  FROM (SELECT d.*,
               COUNT(DISTINCT sex) OVER (PARTITION BY deptname) sex_count
          FROM departments d)
 WHERE sex_count > 1
zbwhf8kr

zbwhf8kr2#

您可以:

select *
from departments
where deptname in (
  select deptname from departments 
  group by deptname having count(distinct sex) > 1
)
wdebmtf2

wdebmtf23#

在Oracle 12中,您可以用途:

SELECT deptname, sex
FROM   departments
MATCH_RECOGNIZE(
  PARTITION BY deptname
  ORDER BY sex
  ALL ROWS PER MATCH
  PATTERN (^ female+ male+ $)
  DEFINE
    female AS sex = 'F',
    male   AS sex = 'M'
);

其输出:
| DEPTNAME|性|
| - ------|- ------|
| 英语|F级|
| 英语|男|
| 销售|F级|
| 销售|F级|
| 销售|男|
| 销售|男|
fiddle

相关问题