代码之家 › 专栏 › 技术社区 › Bodhi

SQL:如何使用子查询返回结果?

subquery sql-server sql

-1

Bodhi · 技术社区 · 8 年前

我正在尝试使用EXISTS从子查询中选择记录

select * from dbo.EMP
where exists (
select a.* from  dbo.HREMP a
inner join dbo.emp  b
on a.ID=b.ID
inner join dbo.LOGO c
on b.id=c.EMPID
inner join dbo.LOGOACC d
on c.BADGENO=d.BADGENO
where d.ACCLVID in (2191,2292,2293,2294,2295,2296,2297))

上面的查询列出了EMP表中的所有员工。?

我的要求是只列出EMP表中存在的、子查询中存在的员工。

3 回复 | 直到 8 年前

D-Shih 8 年前

EXISTS

指定要测试行是否存在的子查询。

在你的情况下 dbo.emp 已在外部 FROM 所以你只需要连接外部 dbo。emp公司 id与 dbo.hremp 中的id 存在 。

你可以试试这个。

SELECT * 
FROM   dbo.emp AS m
WHERE  EXISTS (SELECT 1 
               FROM   dbo.hremp a 
                      INNER JOIN dbo.logo c 
                              ON b.id = c.empid 
                      INNER JOIN dbo.logoacc d 
                              ON c.badgeno = d.badgeno 
               WHERE  d.acclvid IN ( 2191, 2292, 2293, 2294, 
                                     2295, 2296, 2297 ) AND m.id = a.id)

但我认为你可以直接用它来实现你的期望。

SELECT b.* 
FROM   dbo.hremp a 
      INNER JOIN dbo.emp b 
              ON a.id = b.id 
      INNER JOIN dbo.logo c 
              ON b.id = c.empid 
      INNER JOIN dbo.logoacc d 
              ON c.badgeno = d.badgeno 
WHERE  d.acclvid IN ( 2191, 2292, 2293, 2294,2295, 2296, 2297)

编辑

您可以尝试此查询。

SELECT b.* 
FROM   dbo.hremp a 
      INNER JOIN dbo.emp b 
              ON a.id = b.id 
      INNER JOIN dbo.logo c 
              ON b.id = c.empid 
      INNER JOIN dbo.logoacc d 
              ON c.badgeno = d.badgeno 
WHERE EXISTS 
(
    SELECT 1 
    FROM dbo.logoacc m
    WHERE 
        m.acclvid IN ( 2191, 2292, 2293, 2294,2295, 2296, 2297)
    AND 
        m.badgeno = d.badgeno 
)

EXISTS

Kaushik Nayak 8 年前

你需要在 EXISTS 。

SELECT *
FROM   dbo.emp o
WHERE  EXISTS (SELECT 1
               FROM   dbo.hremp a
                      inner join dbo.emp b
                              ON a.id = b.id
                      inner join dbo.logo c
                              ON b.id = c.empid
                      inner join dbo.logoacc d
                              ON c.badgeno = d.badgeno
               WHERE  d.acclvid IN ( 2191, 2292, 2293, 2294,
                                     2295, 2296, 2297 )
                      AND a.id = o.id)  -- correlation between subquery and outer.

我认为这种关系也应该对你有好处?

SELECT *
FROM   dbo.emp b
WHERE  EXISTS (SELECT 1
               FROM   dbo.hremp a
                      inner join dbo.logo c
                              ON a.id = c.empid
                      inner join dbo.logoacc d
                              ON c.badgeno = d.badgeno
               WHERE  d.acclvid IN ( 2191, 2292, 2293, 2294,
                                     2295, 2296, 2297 )
                      AND a.id = b.id)

paparazzo 8 年前

您尚未将main与subquerye连接。

select e.* 
from dbo.EMP e
where exists ( select 1 
                 from dbo.HREMP a 
                 join dbo.LOGO c
                   on c.EMPID = e.id 
                 join dbo.LOGOACC d
                   on d.BADGENO = c.BADGENO  
                  and d.ACCLVID in (2191,2292,2293,2294,2295,2296,2297)
                where a.ID = e.ID
             )

我敢打赌这正是你想要的

select distinct e.* 
from dbo.EMP e
join dbo.HREMP a 
  on a.ID    = e.ID
join dbo.LOGO c
  on c.EMPID = e.id 
join dbo.LOGOACC d
  on d.BADGENO = c.BADGENO  
 and d.ACCLVID in (2191, 2292, 2293, 2294, 2295, 2296, 2297)