查询同一serial且含SM和LG尺寸的devices表数据

表结构 #

create table devices (
  id  number,
  serial number(10,0),
  location varchar2(2),
  size varchar2(2)
);

测试数据 #

insert into devices values (1, 1111, 'Q2', 'SM');
insert into devices values (2, 2222, 'L0', 'SM');
insert into devices values (3, 3333, 'AB', 'MD');
insert into devices values (4, 1111, 'N4', 'LG');
insert into devices values (5, 3333, 'AB', 'SM');
insert into devices values (6, 4444, 'F4', 'LG');
insert into devices values (7, 5555, 'Q9', 'SM');
insert into devices values (8, 4444, 'G1', 'SM');
insert into devices values (9, 1111, 'H1', 'MD');

需求 #

查询所有serial值相同，且对应size值同时包含'SM'和'LG'的行数据，期望结果：

| ID | SERIAL | LOCATION | SIZE |
|----|--------|----------|------|
|  1 |  1111  |   Q2     |  SM  |
|  4 |  1111  |   N4     |  LG  |
|  6 |  4444  |   F4     |  LG  |
|  8 |  4444  |   G1     |  SM  |

方法1：子查询筛选符合条件的serial #

先找出同时拥有'SM'和'LG'的serial，再关联原表获取目标行：

SELECT d.id, d.serial, d.location, d.size
FROM devices d
INNER JOIN (
    SELECT serial
    FROM devices
    WHERE size IN ('SM', 'LG')
    GROUP BY serial
    HAVING COUNT(DISTINCT size) = 2
) s ON d.serial = s.serial
WHERE d.size IN ('SM', 'LG');

方法2：窗口函数（Oracle 11g+） #

通过窗口函数统计每个serial下'SM'和'LG'的存在情况，直接筛选：

SELECT id, serial, location, size
FROM (
    SELECT 
        d.*,
        COUNT(CASE WHEN size = 'SM' THEN 1 END) OVER (PARTITION BY serial) AS cnt_sm,
        COUNT(CASE WHEN size = 'LG' THEN 1 END) OVER (PARTITION BY serial) AS cnt_lg
    FROM devices d
    WHERE size IN ('SM', 'LG')
)
WHERE cnt_sm >= 1 AND cnt_lg >= 1;

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