如何检查表是否在另一个表中有匹配的记录？

  • Z时代
  • 2024-01-10
  • 分类：问答

我有以下表：如何检查表是否在另一个表中有匹配的记录？

表1：AccessMenu

userid item 
1  a 
2  b  
2  c

和

表2：ActionRights

idAdmin itemCol 
2   v 
2   m 
2   d

DESIRED RE SULT：

userid  yes/no 
    1   no 
    2   yes

当我在ActionRight匹配userid到idadmin它应该返回Yes否则No。

我想下面的查询：

select AccessMenu.userid, 
case when ActionRights.IdAdmin=AccessMenu.userid then 'Yes' else 'No' end as 'GRP' 
from AccessMenu left join ActionRights 
on ActionRights.IdAdmin =AccessMenu.userid

但通过这个查询我收到重复的结果为：

userid  yes/no 
    1   no 
    2   yes 
    2   yes 
    2   yes 
    2   yes 
    2   yes

请帮助我。

回答：

SELECT userid, 
     CASE 
     WHEN idadmin IS NULL THEN 'No' 
     ELSE 'Yes' 
     END AS 'Grp' 
FROM (SELECT accessmenu.userid AS UserId, 
       actionrights.idadmin AS IdAdmin 
     FROM accessmenu 
       LEFT JOIN actionrights 
         ON actionrights.idadmin = accessmenu.userid)z

回答：

请检查使用子查询：

select distinct 
    userid, 
    case when (SELECT COUNT(*) from ActionRights b where b.idAdmin=a.userid)>0 then 'Yes' else 'No' end [Yes/No] 
from AccessMenu a

回答：

试试这个

SELECT distinct UserId, CASE WHEN IsAdmin IS NULL THEN 'No' ELSE 'Yes' END AS 'Grp' 
FROM ( 
     select AccessMenu.userid as UserId ,ActionRights.IsAdmin as IsAdmin 
     from AccessMenu left join ActionRights 
     on ActionRights.IsAdmin =AccessMenu.userid 
    )a

看一看

http://www.sqlfiddle.com/#!2/07d52/10

回答：

它很简单。不需要内部查询或子查询。只取 鲜明

SELECT DISTINCT AM.USERID, 
CASE WHEN AR.USERID IS NULL THEN 'NO' ELSE 'YES' END AS [YES/NO] 
FROM #ACCESSMENU AM 
LEFT JOIN #ACCESSRIGHT AR ON AM.USERID=AR.USERID

以上是 如何检查表是否在另一个表中有匹配的记录？ 的全部内容， 来源链接： utcz.com/qa/258619.html

回到顶部