代码之家 › 专栏 › 技术社区 › Tim Schmelter

SQL查询:存在于子表中

exists sql-server sql

Tim Schmelter · 技术社区 · 14 年前

我有两个表tabdata和tabdatadetail。我要父表(tabdata)中所有具有只有子表中的行(tabdatadetail,fk是fidata)具有:

fiactioncode=11 或
fiactioncode=11,fiactioncode=34

任何其他组合都无效。如何得到它们?

我尝试过的却没有成功只有财务代码34):

alt text http://www.bilder-hochladen.net/files/4709-l0.jpg

谢谢你抽出时间。

编辑感谢大家的回答。不幸的是,现在我没有足够的时间来检查哪一个是最好的还是最有效的。我把第一个有效的标记为答案。

伊迪丝2:我认为标记的答案确实是最有效、最紧凑的解决方案。

edit3:codesleuth的答案很有趣,因为它只返回比只有一个fiactioncode=11的行。很难看到,因为它只适用于20个tabdatadetail行或41524189总行中有两个。不管怎样,这不是我所要求的100%,更确切地说是我在寻找什么。

6 回复 | 直到 14 年前

Thomas 14 年前

Select ...
From tabData As T1
Where Exists    (
                Select 1
                From tabDataDetail As TDD1
                Where TDD1.fiData = T1.idData
                    And TDD1.fiactionCode = 11
                )
    And Not Exists    (
                      Select 1
                      From tabDataDetail As TDD1
                      Where TDD1.fiData = T1.idData
                          And TDD1.fiactionCode Not In(11,34)
                    )

为了扩展我的逻辑,第一个检查(更正)是确保存在fiactioncode=11的行。第二个检查通过首先定义我们不需要的行集来工作。我们不想要除fiactioncode=11或34之外的任何东西。因为这是我们不需要的一组项目,所以我们搜索该组中不存在的任何项目。

Lieven Keersmaekers 14 年前

推理

LEFT OUTER JOIN 排除ID与11或34不同的所有IDdata
HAVING 排除所有iddata的只有有34
剩余记录应满足所有约束

测试数据

DECLARE @tabData TABLE (idData INTEGER)
DECLARE @tabDataDetail TABLE (fiData INTEGER, fiActionCode INTEGER)

INSERT INTO @tabData VALUES (1)
INSERT INTO @tabData VALUES (2)
INSERT INTO @tabData VALUES (3)
INSERT INTO @tabData VALUES (4)
INSERT INTO @tabData VALUES (5)

/* Only idData 1 & 2 should be returned */
INSERT INTO @tabDataDetail VALUES (1, 11)
INSERT INTO @tabDataDetail VALUES (2, 11)
INSERT INTO @tabDataDetail VALUES (2, 34)
INSERT INTO @tabDataDetail VALUES (3, 99)
INSERT INTO @tabDataDetail VALUES (4, 11)
INSERT INTO @tabDataDetail VALUES (4, 99)
INSERT INTO @tabDataDetail VALUES (5, 34)

查询

SELECT  *
FROM    @tabData d
        INNER JOIN @tabDataDetail dd ON dd.fiData = d.idData
        INNER JOIN (
          SELECT  idData
          FROM    @tabData d
                  INNER JOIN @tabDataDetail dd ON dd.fiData = d.idData
                  LEFT OUTER JOIN (
                    SELECT  fiData
                    FROM    @tabDataDetail
                    WHERE   fiActionCode NOT IN (11, 34)
                  ) exclude ON exclude.fiData = d.idData
          WHERE   exclude.fiData IS NULL                
          GROUP BY
                  idData
          HAVING  MIN(fiActionCode) = 11        
        ) include ON include.idData = d.idData

Joe Stefanelli 14 年前

根据对其他答案的评论中给出的澄清,编辑了我的答案。

select td.idData
 from tabData td
  left join tabDataDetail tdd
   on td.idData = tdd.fiData
    and tdd.fiActionCode = 11
  left join tabDataDetail tdd2
   on td.idData = tdd2.fiData
    and tdd2.fiActionCode = 34
  left join tabDataDetail tdd3
   on td.idData = tdd3.fiData
    and tdd3.fiActionCode not in (11,34)
 where (tdd.fiData is not null
  or (tdd.fiData is not null and tdd2.fiData is not null))
  and tdd3.fiData is null
 group by td.idData

StuartLC 14 年前

编辑:apols-我明白你对儿童行的意思。这不是特别有效。还要感谢利文提供的数据。

SELECT idData FROM
tabData td
WHERE EXISTS 
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode = 11
 )
AND NOT EXISTS
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode <> 11
 )
UNION
SELECT idData 
    FROM tabData td
    WHERE EXISTS 
    (
        SELECT 1 
            FROM tabDataDetail tdd 
            WHERE tdd.fiData = td.idData AND fiActionCode = 11
     )
    AND EXISTS
    (
        SELECT 1 
            FROM tabDataDetail tdd 
            WHERE tdd.fiData = td.idData AND fiActionCode = 34
     )
AND NOT EXISTS
(
    SELECT 1 
        FROM tabDataDetail tdd 
        WHERE tdd.fiData = td.idData AND fiActionCode NOT IN (11, 34)
 )

Community c0D3l0g1c 7 年前

谢谢@ Lieven 对于要测试的数据代码:

DECLARE @tabData TABLE (idData INTEGER)
DECLARE @tabDataDetail TABLE (idDataDetail int IDENTITY(1,1),
    fiData INTEGER, fiActionCode INTEGER)

INSERT INTO @tabData VALUES (1)
INSERT INTO @tabData VALUES (2)
INSERT INTO @tabData VALUES (3)
INSERT INTO @tabData VALUES (4)
INSERT INTO @tabData VALUES (5)

/* Only idData 1 & 2 should be returned */
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (1, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (2, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (2, 34)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (3, 99)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (4, 11)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (4, 99)
INSERT INTO @tabDataDetail (fiData,fiActionCode) VALUES (5, 34)

查询:

SELECT  td.idData
FROM    @tabData td
        INNER JOIN @tabDataDetail tdd ON td.idData = tdd.fiData
WHERE   tdd.fiActionCode = 11 -- check 11 exists
        AND NOT EXISTS ( SELECT * FROM @tabDataDetail WHERE fiData = td.idData
                          AND idDataDetail <> tdd.idDataDetail )
            -- ensures *only* 11 exists (0 results from subquery)
UNION
SELECT  td.idData
FROM    @tabData td
        INNER JOIN @tabDataDetail tdd1 ON td.idData = tdd1.fiData
        INNER JOIN @tabDataDetail tdd2 ON td.idData = tdd2.fiData
WHERE   tdd1.fiActionCode = 11 -- check 11 exists
        AND tdd2.fiActionCode = 34 -- check 34 exists

idData
-----------
1
2

(2 row(s) affected)

这里只有一个子查询(它是 COUNT 而不是很慢 NOT EXISTS )这就创建了一个非常整洁的执行计划,如果您在速度方面遇到问题,它应该会有所帮助。

Martin Smith 14 年前

我想这是通过一次数据传递来实现的。

这取决于数据分布是否比两个独立的查找更好。

WITH matches AS
(
SELECT fiData
FROM tabDataDetail 
GROUP BY fiData
HAVING COUNT(CASE WHEN fiactionCode = 11 THEN 1 END) > 0
AND COUNT(CASE WHEN fiactionCode NOT IN (11,34) THEN 1 END) = 0
)
SELECT ...
FROM idData i
JOIN matches m
ON  m.fiData = i.idData