SQL查询以获取没有关联服务的用户

Question

我把下面的数据放在一个表格里。我试图获取所有的“调制解调器”用户，他们没有相关的电话服务。

UserID   DeviceNumber   DeviceType    DeviceRole
1        A              Telephone     SingleUser
1        A              Modem         MultiUser
1        B              Modem         MultiUser
2        C              Telephone     SingleUser
2        C              Modem         MultiUser
2        D              Modem         MultiUser

select distinct t.* from table t
join table t1 on t1.UserID= v.UserID
and t1.DeviceNumber <> t.DeviceNumber
and t.DeviceType = 'Modem';

我想在输出中看到DeviceNumber B和D。但是上面的查询没有返回预期的结果。

Answer 1

嗯嗯。。。一种方法是：

select t.*
from t
where t.devicetype = 'Modem' and
      not exists (select 1
                  from t t2
                  where t2.userid = t.userid and t2.devicenumber = t.devicenumber and
                        t2.devicetype = 'Telephone'
                 );

Answer 2

你可以用count来做。这是演示。

select
   UserID,
   DeviceNumber,
   DeviceType,
   DeviceRole
from
(
select
    yt.*,
    count(*) over (partition by DeviceNumber) as cnt
from yourTable yt

) val

where cnt = 1
and DeviceType = 'Modem'

输出：

​

​