英文:
Need query to display the departments which are having both MALE and FEMALE Employees
问题
需要查询以显示同时拥有男性和女性员工的部门。
SELECT deptname, sex
FROM departments
WHERE deptname IN (SELECT deptname FROM departments WHERE sex = 'M')
AND deptname IN (SELECT deptname FROM departments WHERE sex = 'F');
期望的输出为：
Deptname Sex
SALES M
SALES F
SALES M
SALES F
ENG M
ENG F
英文:
Need query to display the departments which are having both MALE and FEMALE Employees
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
Please help me.
Thanks in Advance.
答案1
得分: 1
使用窗口函数来避免两次查询表：
SELECT *
FROM (SELECT d.*,
COUNT(DISTINCT sex) OVER (PARTITION BY deptname) sex_count
FROM departments d)
WHERE sex_count > 1
英文:
Or using a windowing function to avoid hitting the table twice:
SELECT *
FROM (SELECT d.*,
COUNT(DISTINCT sex) OVER (PARTITION BY deptname) sex_count
FROM departments d)
WHERE sex_count > 1
答案2
得分: 0
以下是翻译好的内容：
可以执行以下操作：
选择 *
从部门
其中部门名称在 (
选择部门名称从部门
按部门名称分组，具有计数（不同性别）> 1
)
请告诉我如果您需要任何其他的翻译帮助。
英文:
You can do:
select *
from departments
where deptname in (
select deptname from departments
group by deptname having count(distinct sex) > 1
)
答案3
得分: 0
从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 | SEX |
|---|---|
| ENG | F |
| ENG | M |
| SALES | F |
| SALES | F |
| SALES | M |
| SALES | M |
英文:
From Oracle 12, you can use:
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'
);
Which outputs:
| DEPTNAME | SEX |
|---|---|
| ENG | F |
| ENG | M |
| SALES | F |
| SALES | F |
| SALES | M |
| SALES | M |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论