MySQL 显示值为 NULL 或等于 X 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8775098/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL display rows where value IS NULL or equal to X
提问by Sweepster
userPosts.value can contain one of two values: 0 or 1.
userPosts.value 可以包含两个值之一：0 或 1。
I am Left Joining userPosts to my Posts table.
我将 userPosts 加入我的 Posts 表。
I want to get all posts from my Posts table where userPosts.value = 0 as well as all posts that do not have any userPosts.value at all (thus, NULL).
我想从我的 Posts 表中获取所有帖子，其中 userPosts.value = 0 以及所有根本没有任何 userPosts.value 的帖子（因此，NULL）。
The following only get me posts where value = 0 but no NULL:
以下仅让我发布 value = 0 但没有 NULL 的帖子：
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value != 1
ORDER BY $wpdb->posts.post_date DESC
The following only gets me posts where value = NULL:
以下仅让我发布 value = NULL 的帖子：
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
but this yields no results at all:
但这根本不会产生任何结果：
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
AND userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
and this does get me posts with value = 0 as well as NULL but it repeats all my NULL posts three times!
这确实让我发布了 value = 0 以及 NULL 的帖子，但它重复了我所有的 NULL 帖子三遍！
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userPosts.value = 0
OR userPosts.value IS NULL
ORDER BY $wpdb->posts.post_date DESC
So what am I doing wrong?
那么我做错了什么？
回答by
Try to use parenthasis on ORcondition (userContests.value = 0 OR userContests.value IS NULL)
尝试在OR条件下使用括号(userContests.value = 0 OR userContests.value IS NULL)
SELECT * FROM $wpdb->posts
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE (userContests.value = 0
OR userContests.value IS NULL)
ORDER BY $wpdb->posts.post_date DESC
回答by Marc B
(x = 0) AND (x is NULL)- a single field cannot be two values at the same time. No surprise that you get no results at all, because you've specified a where condition that is impossible to satisfy.
(x = 0) AND (x is NULL)- 单个字段不能同时是两个值。您根本没有得到任何结果也就不足为奇了，因为您指定了一个不可能满足的 where 条件。
As for the rest of the query. You use $wdpd->postsas your source table, but then use a table named userContestsin the where clause. Does $wpdb->posts resolve to userContests? If so, why make the table name dynamic in one place and hard-coded it in another?
至于其余的查询。您$wdpd->posts用作源表，但随后使用userContests在 where 子句中命名的表。$wpdb->posts 是否解析为 userContests？如果是这样，为什么要在一个地方动态地设置表名，而在另一个地方硬编码呢？
回答by Bohemian
You partially answered your own question in the title: OR not AND, but try using DISTINCT:
您在标题中部分回答了您自己的问题：OR not AND，但尝试使用DISTINCT：
SELECT DISTINCT * FROM $wpdb->posts -- Note "DISTINCT"
LEFT JOIN userPosts ON ($wpdb->posts.ID = userPosts.postID)
WHERE userContests.value = 0
OR userContests.value IS NULL -- Note "OR"
ORDER BY $wpdb->posts.post_date DESC
