代码之家 › 专栏 › 技术社区 › Jay Askren

如何创建一个表示字段不等于X的连接?

join database sql

4

Jay Askren · 技术社区 · 15 年前

PERSON_T              DISEASE_T               DRUG_T
=========             ==========              ========
PERSON_ID             DISEASE_ID              DRUG_ID
GENDER                PERSON_ID               PERSON_ID
NAME                  DISEASE_START_DATE      DRUG_START_DATE
                      DISEASE_END_DATE        DRUG_END_DATE

我想写一个查询,找到所有的人谁有疾病52号,但没有采取药物34。我该怎么做?我在MySql中尝试了以下方法:

SELECT p.person_id, p.gender, p.name, disease_id, drug_id 
   FROM person_t as p 
   INNER JOIN disease_t on disease_t.person_id = p.person_id 
   RIGHT OUTER JOIN drug_t on drug_t.person_id = p.person_id 
   WHERE disease_id= 52 AND drug_id != 34;

3 回复 | 直到 15 年前

1

7

OMG Ponies 15 年前

对于不想使用子查询的用户:

   SELECT p.person_id, p.gender, p.name, disease_id
     FROM PERSON_T p 
     JOIN DISEASE_T d ON d.person_id = p.person_id 
LEFT JOIN DRUG_T dt ON dt.person_id = p.person_id
                   AND dt.drug_id = 34
    WHERE disease_id = 52
      AND dt.person_id IS NULL

2

10

Mark Byers 15 年前

SELECT p.person_id, p.gender, p.name, disease_id
FROM person_t as p 
INNER JOIN disease_t d on disease_t.person_id = p.person_id 
WHERE disease_id = 52
AND p.person_id NOT IN (SELECT person_id IN drug_t WHERE drug_id = 34)

3

6

Adam Ruth 15 年前

取决于优化器不存在可能比不存在更有效。两个都试试看哪一个效果最好。

SELECT p.person_id, p.gender, p.name, disease_id, drug_id 
   FROM person_t as p 
   INNER JOIN disease_t on disease_t.person_id = p.person_id 
   WHERE disease_id= 52 AND NOT EXISTS (
       SELECT * from drug_T WHERE person_id = person_t.person_id AND drug_id = 34)