计算表中出现的记录是其中之一，而不是其他：MYSQL
我有两个简单的表
users
+----+--------+-----------+
| id | gender | birthdate |
+----+--------+-----------+
userpreference
+----+------------------+-----------------+
| id | preference value | preference type |
+----+------------------+-----------------+
问题：
我想查询所有未列出特定偏好值（例如“购物”）的人。这包括所有列出了特定偏好值的人。也没有列出任何偏好类型，因此该列可能为空，但是由于用户偏好的列“id”将用户引用为外键，我还想将所有未出现在第二个表中的人（用户偏爱）？
没有偏好值“购物”作为偏好值的总人数：
这是我尝试过的：
SELECT
(
SELECT COUNT(DISTINCT userpreference.id) FROM userpreference
WHERE preferencevalue != 'shopping')
+
(
SELECT COUNT(users.id)
FROM users
WHERE users.id NOT IN
(SELECT userpreference.Id
FROM userpreference )
)
AS'Total'
I have a two simple tables
users
+----+--------+-----------+
| id | gender | birthdate |
+----+--------+-----------+
userpreference
+----+------------------+-----------------+
| id | preference value | preference type |
+----+------------------+-----------------+
Question:
I want to query all people who have not listed a specific preference value such as 'shopping'.This includes all people who have not listed any preference types as well so that column could be null, however since userpreference's column 'id' references users as a foreign key, I also want to include in my count all people who don't show up in the second table (user preference)?
Total # of people who do not have preference value 'shopping' as their preference value:
Here is what i have tried:
SELECT
(
SELECT COUNT(DISTINCT userpreference.id) FROM userpreference
WHERE preferencevalue != 'shopping')
+
(
SELECT COUNT(users.id)
FROM users
WHERE users.id NOT IN
(SELECT userpreference.Id
FROM userpreference )
)
AS'Total'
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
尝试 RIGHT JOIN，这将包括所有未出现在第二个表中的人
SELECT *
来自
用户RIGHT JOIN 用户首选项 ON ( users.
userID= Users.userID)WHERE首选项值='购物'
Try a RIGHT JOIN, that will include all people who dont show up in the second table
SELECT *
FROM
UsersRIGHT JOIN Userpreference ON ( users.
userID= Users.userID)WHERE preference_value = 'shopping'
试试这个：
SELECT COUNT(DISTINT U.id) FROM users U NATURAL LEFT JOIN userpreference UP
WHERE UP.preferencevalue IS NULL OR UP.preferenceValue != '购物';
LEFT JOIN 应该引入所有用户记录，无论他们是否有 UP 记录。
Try this:
SELECT COUNT(DISTINT U.id) FROM users U NATURAL LEFT JOIN userpreference UP
WHERE UP.preferencevalue IS NULL OR UP.preferenceValue != 'shopping';
The LEFT JOIN should bring in all the users records whether or not they have a UP record.