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

ni65a41a 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(313)

我试图选择客户失踪或从来没有一个生日输入。这是一种旧系统，允许生日数据： 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

mysql

来源：https://stackoverflow.com/questions/48251595/selecting-records-that-are-missing-a-birthday-row-or-value-from-database

1条答案

按热度按时间

8dtrkrch1#

条件 birthday.name = 'date_of_birth' 应该在左连接的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')

也可以使用“不存在”条件：

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')
  )

赞(0）回复(0）举报 2021-06-24

我来回答

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

1条答案

相关问题

热门标签

最新问答