Oracle中employee表基于emp_type的特殊查询逻辑实现求助

需求回顾 #

当表employee中存在emp_type为100的员工时，仅返回这些符合条件的行；如果没有任何员工的emp_type是100，则返回表中所有数据。

测试数据与表结构 #

先明确你的表定义和测试数据：

-- 创建表
CREATE TABLE employee(emp_no NUMBER, emp_id VARCHAR2(50), emp_type NUMBER);

-- 测试数据1：存在emp_type=100的员工
INSERT INTO employee(emp_no, emp_id, emp_type) VALUES (1, 'John', 100);
INSERT INTO employee(emp_no, emp_id, emp_type) VALUES (2, 'Sam', 200);

-- 测试数据2：所有员工emp_type都不为100
-- 先清空表再插入
TRUNCATE TABLE employee;
INSERT INTO employee(emp_no, emp_id, emp_type) VALUES (1, 'John', 200);
INSERT INTO employee(emp_no, emp_id, emp_type) VALUES (2, 'Sam', 200);

方法1：使用EXISTS子查询（最简洁直观） #

这是最直接的写法，利用EXISTS判断表中是否存在目标类型，再通过逻辑或组合筛选条件：

SELECT emp_no, emp_id, emp_type
FROM employee
WHERE emp_type = 100
   OR NOT EXISTS (SELECT 1 FROM employee WHERE emp_type = 100);

逻辑解释：

• 如果表中存在emp_type=100的记录，NOT EXISTS返回假，只有emp_type=100的行会被选中；
• 如果表中没有emp_type=100的记录，emp_type=100的条件不成立，NOT EXISTS返回真，所有行都会被返回。

方法2：使用窗口函数（适合更复杂的扩展场景） #

如果后续需要扩展更多条件，窗口函数的方式更灵活，通过统计目标类型的数量来判断：

SELECT emp_no, emp_id, emp_type
FROM (
    SELECT emp_no, emp_id, emp_type,
           -- 统计全表中emp_type=100的行数
           COUNT(CASE WHEN emp_type = 100 THEN 1 END) OVER() AS cnt_100
    FROM employee
) t
WHERE emp_type = 100 OR cnt_100 = 0;

逻辑解释：

• 子查询中用COUNT() OVER()全局统计emp_type=100的记录数；
• 外层根据统计值筛选：如果cnt_100>0，只选emp_type=100的行；如果cnt_100=0，返回所有行。

测试验证 #

• 用测试数据1执行查询，会返回John的那条记录；
• 用测试数据2执行查询，会返回John和Sam两条记录，完全符合你的需求。

内容的提问来源于stack exchange，提问作者Shashank Jha