从出现的表中计数记录是一个，但不是其他:MYSQL [英] Counting records from table that appear is one but not other: MYSQL

问题描述

我有两个简单的表

用户

+----+--------+-----------+
| id | gender | birthdate |
+----+--------+-----------+

用户首选项

+----+------------------+-----------------+
| id | preference value | preference type |
+----+------------------+-----------------+

问题:

我想查询所有未列出特定首选项值的人，例如'购物'，这也包括所有未列出任何首选项类型的人，因此该列可以为null，但是由于userpreference的列'id'引用用户作为外键，我还想将所有未出现在第二个表中的用户包括在内(用户首选项)?

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:

这是我尝试过的:

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'

推荐答案

Select Count(*)
From Users
Where Not Exists   (
                    Select 1
                    From UserPreference As UP1
                    Where UP1.id = Users.id
                        And UP1.PreferenceValue = 'Shopping'
                    )

这篇关于从出现的表中计数记录是一个，但不是其他:MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！