单SQL语句实现带自关联条件的药品处方数据筛选（避免代码重复）

阿华AIGC实验室

2026-4-2

没问题，我完全懂你的痛点——当查询逻辑涉及多表关联、复杂筛选条件时，重复写相同的代码不仅繁琐，后续调整时还很容易漏改某一处，导致数据不一致。针对你的需求，我们可以用**ANSI SQL标准的公共表表达式（CTE）**来封装重复逻辑，既能保证可读性，又能彻底解决代码冗余问题，Oracle也完美支持这种写法。

核心思路

我们把两个查询共享的关键逻辑（2020年领取过x类药的患者ID）封装成一个可复用的CTE，然后基于这个CTE一次性获取你需要的两类数据：

2020年领取的x类药记录（即原来的mytab内容）
上述患者在2020年领取的y类药记录

完整ANSI SQL代码

WITH eligible_patients AS (
    -- 封装核心筛选条件：2020年领取过x类药的唯一患者ID
    SELECT DISTINCT t1.id
    FROM tab1 t1
    JOIN tab2 t2 ON t1.code = t2.code
    WHERE t1.dte BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
      AND t2.label = 'x'
),
x_dispenses_2020 AS (
    -- 原来的mytab数据：2020年x类药的所有领取记录
    SELECT t1.*
    FROM tab1 t1
    JOIN tab2 t2 ON t1.code = t2.code
    WHERE t1.dte BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
      AND t2.label = 'x'
)
-- 合并两类数据，用record_type区分来源
SELECT 'x_dispense' AS record_type, id, dte, code
FROM x_dispenses_2020
UNION ALL
SELECT 'eligible_patient_y_dispense' AS record_type, t1.id, t1.dte, t1.code
FROM tab1 t1
JOIN tab2 t2 ON t1.code = t2.code
JOIN eligible_patients ep ON t1.id = ep.id
WHERE t1.dte BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
  AND t2.label = 'y';

代码解释

eligible_patients CTE：
- 只做一件事：找出所有在2020年领取过x类药的患者ID，用DISTINCT去重，避免同一个患者ID多次出现。
- 这是两个查询共享的核心条件，后续如果要调整年份、药品标签，只需要改这一处。
x_dispenses_2020 CTE：
- 对应你原来创建mytab的查询逻辑，单独封装后更清晰，也方便后续单独调用。
最终合并查询：
- 用UNION ALL合并两个结果集，加record_type列是为了区分每条记录是x类药记录，还是符合条件的患者的y类药记录（如果不需要区分，也可以去掉这个列，直接合并）。
- 所有重复的筛选条件（比如日期范围、tab1和tab2的关联逻辑）都只写一次，彻底避免代码冗余。

扩展优化（针对大数据量场景）

考虑到你的tab1每月有10亿行数据，建议：

给tab1.dte、tab1.code、tab1.id建立复合索引
给tab2.code建立唯一索引（因为你提到tab2的code没有重复）
如果不需要区分两类数据，只是想一次性获取这些患者2020年的所有x/y类药记录，可以简化成：

WITH eligible_patients AS (
    SELECT DISTINCT t1.id
    FROM tab1 t1
    JOIN tab2 t2 ON t1.code = t2.code
    WHERE t1.dte BETWEEN DATE '2020-01-01' AND DATE '2020-12-31'
      AND t2.label = 'x'
)
SELECT t1.*, t2.label
FROM tab1 t1
JOIN tab2 t2 ON t1.code = t2.code
JOIN eligible_patients ep ON t1.id = ep.id
WHERE t1.dte BETWEEN DATE '2020-01-01' AND DATE '2020-12-31';

这个查询会直接返回所有符合条件的患者2020年的x/y类药记录，同时附带药品标签，方便你后续按需筛选。