如何查询多行组合(MySQL)

Question

我正在编写一个查询，能够检查同一用户的多行。如果同一用户的记录组合没有提供我需要的信息(请记住，它是用户拥有的所有记录的组合，而不是单个记录的组合)，则认为该用户已通过。

例如:有两个表。

一个是"user“，保存用户的个人信息：

id client_id first_name last_name date_of_birth ssn地址另一个是“筛选”，它保存用户的体检信息：

id、user_id、日期、胆固醇、低密度脂蛋白、血红蛋白、甘油三酯、mcv、葡萄糖、mchc、ha1c、血小板计数。一个用户在用户表中只能有一条记录，但在筛选表中可以有多条记录。我想要做的是检查属于同一用户的用户的多个筛选记录，看看这些记录的组合是否提供了我需要的必要信息。如果不是，则选择该用户。例如，必要的信息包括胆固醇、低密度脂蛋白、甘油三酯、葡萄糖或。如果用户有两个筛选记录，一个记录提供胆固醇(非空)，另一个记录提供甘油三酯(非空)，葡萄糖(非空)和ha1c(非空)。他被选中是因为缺少低密度脂蛋白。

我如何编写能够做到这一点的查询呢？我尝试过内部连接，但似乎不起作用。这里有一些要求。对于胆固醇、低密度脂蛋白和甘油三酯，只要其中一种缺失，就应该选择使用者。但是对于葡萄糖和ha1c，只有当两者都缺失时，用户才会被选中。

我尝试过的一个查询是这样的。它显示了不应该显示的用户。请帮我看一下，告诉我出了什么问题：

SELECT users.id AS user_id, users.first_name, users.last_name, clients.name AS client, 
        users.social_security_number AS ssn, users.hiredate, hra.id AS hra_id, hra.date     AS hra_date, hra.maileddate AS hra_maileddate, 
        sall.id AS screening_id, sall.date AS screening_date, sall.maileddate AS     screening_maileddate
    FROM users 
    INNER JOIN clients
    ON(
       users.client_id = clients.id
       AND users.client_id = '1879'
       )
    INNER JOIN hra
    ON(
       users.id = hra.user_id
       AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
       AND hra.maileddate IS NOT NULL 
       )
     INNER JOIN screening sall
     ON(
     sall.user_id = users.id 
     )
      INNER JOIN screening s1
      ON(
      s1.user_id = users.id

      AND s1.date BETWEEN '2011-05-15' AND '2011-11-15'
    ) 
    INNER JOIN screening s2
    ON(
       s2.user_id = users.id

      AND s2.date BETWEEN '2011-05-15' AND '2011-11-15'
    ) 
    INNER JOIN screening s3
    ON(
      s3.user_id = users.id

      AND s3.date BETWEEN '2011-05-15' AND '2011-11-15'
    )
    INNER JOIN screening s4 
    ON(
       s4.user_id = users.id

      AND s4.date BETWEEN '2011-05-15' AND '2011-11-15'
    )
    INNER JOIN screening s5
    ON(
       s5.user_id = users.id

      AND s5.date BETWEEN '2011-05-15' AND '2011-11-15'
  )
    INNER JOIN screening s6
    ON(
      s6.user_id = users.id

      AND s6.date BETWEEN '2011-05-15' AND '2011-11-15'
  )

    WHERE ((s1.cholesterol IS NULL
    OR s2.ldl IS NULL
    OR s3.triglycerides IS NULL)
    OR (s4.glucose IS NULL
    AND s5.ha1c IS NULL))
    AND s6.maileddate IS NULL
    GROUP BY users.id

Answer 1

你不想要内部连接，你想要左连接。当任何联接条件失败时，Inner join将禁止显示结果行，而left join则允许结果行显示为null项。

你离这里很近了。

Answer 2

你真的要做所有内部连接的筛选吗？考虑一下这一点(这不是一个完整的查询，但建议使用不同的筛选方法:)

SELECT users.id AS user_id, users.first_name, users.last_name, clients.name AS client, 
    users.social_security_number AS ssn, users.hiredate, hra.id AS hra_id, hra.date     AS hra_date, hra.maileddate AS hra_maileddate, 
    sall.id AS screening_id, sall.date AS screening_date, sall.maileddate AS     screening_maileddate
FROM users 
LEFT JOIN clients
ON(
   users.client_id = clients.id
   AND users.client_id = '1879'
)
LEFT JOIN hra
ON(
   users.id = hra.user_id
   AND hra.date BETWEEN '2011-07-01' AND '2011-11-15'
   AND hra.maileddate IS NOT NULL 
)
LEFT JOIN screening sall
ON(
   sall.user_id = users.id 
)
WHERE users.id NOT IN
(
  SELECT user_id FROM screening1
  WHERE user_id = users.id AND
        colesterol IS NULL
        date BETWEEN '2011-05-15' AND '2011-11-15'
) 
OR users.id NOT IN
(
  SELECT user_id FROM screening2
  WHERE user_id = user.id AND
        ldl IS NULL
        date BETWEEN '2011-05-15' AND '2011-11-15'
) 
...