SQL 多个 OR BETWEENS

Question

我在 Postgres 上工作，下面的查询似乎永远不会完成执行，但它只是导致问题的最终 OR，如果我将其注释掉，它运行良好。仅仅是因为它是一种低效的编写方式吗？如果是这样，我想不出替代方案。

SELECT a.subject_id, a.hadm_id, a.admittime, d.icd9_code, l.short_title, d.seq_num
FROM mimiciii.admissions a
INNER JOIN mimiciii.diagnoses_icd d
ON  a.subject_id = d.subject_id
AND a.hadm_id = d.hadm_id
INNER JOIN mimiciii.d_icd_diagnoses l
ON d.icd9_code = l.icd9_code
WHERE a.subject_id IN
    (SELECT DISTINCT d.subject_id
     FROM mimiciii.diagnoses_icd d
     WHERE d.icd9_code BETWEEN '390%' and '459%')
     AND d.icd9_code NOT IN 
     (SELECT d.icd9_code
         FROM mimiciii.diagnoses_icd d
         WHERE d.icd9_code BETWEEN 'V01%' AND 'V91%'
         OR d.icd9_code BETWEEN 'E000%' AND 'E999%'
         OR d.icd9_code BETWEEN '630%' AND '679%'
         OR d.icd9_code BETWEEN '760%' AND '999%'
          )
ORDER BY subject_id, admittime

Answer 1

也许它从未完成执行的原因之一是 BETWEEN 的过滤器 all 行。 BETWEEN 不能使用通配符。

整个子查询似乎是不必要的。您可以将其删除。试试这个 where 子句:

WHERE a.subject_id IN (SELECT d.subject_id
                       FROM mimiciii.diagnoses_icd d
                       WHERE d.icd9_code >= '390' and 
                             d.icd9_code < '460'
                      )

如果代码符合此条件，则它不符合您的其他条件。

我怀疑查询还有其他问题，但这个问题很明显。