mysql - 从数据库中选择缺少生日行或值的记录

Question

我正在尝试选择缺失或从未输入过生日的客户。这是一种旧系统，允许生日数据为:false、0 或 [blank] 表示客户端有生日但无论出于何种原因，它已被删除。

日期的排列方式如下。客户的名字和姓氏以及类别类型存在于客户表中，而所有其他数据存储在不同表中的行而不是列中。

我已通过 class_type = 2 AND sub_class_type = 5 过滤了下面的表格布局，以简化数据示例。

根据下面的表格布局，我应该返回客户端 ID:5、11。

客户端 ID:5 - 从未输入过生日。不存在行。

客户端 ID:11 - 已删除生日值

我的下面的查询仅返回客户端 ID:11

对此的任何帮助都将是救星!

客户表布局

+----+------------+-----------+------------+----------------+
| id | first_name | last_name | class_type | sub_class_type |
+----+------------+-----------+------------+----------------+
| 5  | Aaron      | Smith     | 2          | 5              |
| 6  | Jenny      | Mackey    | 2          | 5              |
| 11 | Walter     | Walt      | 2          | 5              |
+----+------------+-----------+------------+----------------+

客户附加表格布局

+----+-----------+------------------------+----------------+
| id | client_id | name                   | value          |
+----+-----------+------------------------+----------------+
| 51 | 5         | work_email_address     | test@test.com  |
| 83 | 6         | date_of_birth          | 07/01/1981     |
| 91 | 6         | work_email_address     | test1@test.com |
| 86 | 11        | cell_phone             | 222-999-8888   |
| 90 | 11        | work_email_address     | test2@test.com |
| 92 | 11        | date_of_birth          |                |
+----+-----------+------------------------+----------------+

我的查询

SELECT 
  c.id, c.first_name, c.last_name
FROM 
  app_100_clients AS c 
  LEFT JOIN app_100_client_additional_data AS birthday ON birthday.client_id = c.id 
WHERE 
  c.class_type = 2 
  AND c.sub_class_type = 5 
  AND (
    birthday.name = 'date_of_birth' 
    AND (birthday.value = '') 
    OR (birthday.value = '0') 
    OR (birthday.value = 'false') 
    OR (birthday.value IS NULL)
  )
GROUP BY 
  c.id 

Answer 1

条件birthday.name = 'date_of_birth' 应位于 LEFT JOIN 的 ON 子句中:

SELECT 
  c.id, c.first_name, c.last_name
FROM 
  app_100_clients AS c 
  LEFT JOIN app_100_client_additional_data AS birthday
    ON  birthday.client_id = c.id 
    AND birthday.name = 'date_of_birth' 
WHERE 
  c.class_type = 2 
  AND c.sub_class_type = 5 
  AND (
       (birthday.value = '') 
    OR (birthday.value = '0') 
    OR (birthday.value = 'false') 
    OR (birthday.value IS NULL)
  )
GROUP BY 
  c.id 

演示:http://sqlfiddle.com/#!9/49cc9e

我还将把birthday.value的条件重写为

AND (
  birthday.value IN ('', '0', 'false') 
  OR birthday.value IS NULL
)

或

AND coalesce(birthday.value, '') IN ('', '0', 'false')

您还可以使用 NOT EXISTS 条件:

SELECT c.id, c.first_name, c.last_name
FROM app_100_clients AS c 
WHERE c.class_type = 2 
  AND c.sub_class_type = 5
  AND NOT EXISTS (
    SELECT *
    FROM app_100_client_additional_data AS birthday
    WHERE birthday.name = 'date_of_birth'
      AND birthday.client_id = c.id
      AND birthday.value NOT IN ('', '0', 'false')
  )