计算在x条件下与表无关的行数

Question

我有一个users表和一个hobbies表

users

id | name   |
---+--------+
1  | John   |
2  | Jim    |
3  | Karen  |

hobbies

id | user_id | hobby   |
---+---------+---------+
1  | 1       | drawing |
2  | 1       | singing |
3  | 2       | coding  |
4  | 2       | drawing |
6  | 3       | chess   |
7  | 3       | coding  |

我需要一个SQL查询，它可以计算出有多少用户没有“画画”或“唱歌”作为业余爱好。在本例中，只有Karen会被计算在内，因为他们是唯一不喜欢唱歌或画画的人。

Answer 1

您可以使用not exists操作符：

SELECT *
FROM   users u
WHERE  NOT EXISTS (SELECT *
                   FROM   hobbies h
                   WHERE  hobby IN ('drawing', 'singing') AND
                          h.user_id = u.id)

Answer 2

您可以使用not exists。。。两次：

select u.*
from users u
where not exists (select 1 from hobbies h where h.user_id = u.id and h.hobby = 'singing') and
      not exists (select 1 from hobbies h where h.user_id = u.id and h.hobby = 'dancing') ;

Answer 3

简单

declare @users table(id int, name varchar(15))
declare @hobbies table(id int, user_id int, hobby varchar(15))

insert into @users
values
 (1,'John'),
 (2,'Jim'),
 (3,'Karen')

insert into @hobbies
values
 (1,1,'drawing'),
 (2,1,'singing'),
 (3,2,'coding'),
 (4,2,'drawing'), 
 (6,3,'chess'),
 (7,3,'coding')


select count(*)
from @users u
left join
(select u.id
from @users u
    inner join @hobbies h
    on h.user_id = u.id
where h.hobby in ('drawing','singing'))users_with_hobbies  on users_with_hobbies.id = u.id
where users_with_hobbies.id is null

-- or

select count(*)
from @users u   
where not id in (select u.id
   from @users u
    inner join @hobbies h
    on h.user_id = u.id
where h.hobby in ('drawing','singing'))

-- or   
select count(*)
from @users u   
where not exists (select null
  from @hobbies h   
  where h.user_id = u.id and h.hobby in ('drawing','singing'))