Вопросы Теги

SQL-Query: EXISTS в Subtable

У меня есть две таблицы tabData и tabDataDetail. Я хочу, чтобы все idData (PK) из Parent-Table (tabData) имели только только строк в Child-Table (tabDataDetail, FK - это fiData) только с:

  • fiActionCode = 11 или
  • fiactionCode = 11 и fiActionCode = 34

Любая другая комбинация недопустима. Как их получить?

Что я пытался без успеха (медленно и дает мне также строки, которые имеют только только fiActioncode 34):

alt text
(источник: bilder-hochladen.net )

Спасибо за ваше время.

РЕДАКТИРОВАТЬ : Спасибо всем за ответы. Теперь, к сожалению, у меня недостаточно времени, чтобы проверить, какой из них лучше или работает вообще. Первый рабочий я пометил как ответ.

EDIT2: я думаю, что помеченный ответ действительно является наиболее эффективным и компактным решением.

EDIT3: Ответ Codesleuth интересен, потому что он возвращает только строки, которые имеют только один fiActionCode = 11. Трудно увидеть, потому что это верно только для 20 tabDataDetail-rows из 41524189 тотальных строк, которые имеют две. Во всяком случае, это не было 100%, что я я спросил, а точнее то, что я искал.

12
задан Glorfindel 5 August 2019 в 19:12
поделиться

6 ответов

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. Поскольку это набор элементов, которые нам не нужны, мы ищем все, что не существует в этом наборе.

5
ответ дан 2 December 2019 в 21:22
поделиться

Редактировать: 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)
 )
1
ответ дан 2 December 2019 в 21:22
поделиться

Спасибо @ Ливен за код данных для проверки: 

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)

Здесь только 1 подзапрос (и это COUNT вместо очень медленного NOT EXISTS ) создается очень аккуратный план выполнения, который должен помочь, если у вас возникнут проблемы со скоростью.

1
ответ дан 2 December 2019 в 21:22
поделиться

Отредактировал свой ответ на основании разъяснений, данных в комментариях к другим ответам. 

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
1
ответ дан 2 December 2019 в 21:22
поделиться

Рассуждение

  1. LEFT OUTER JOIN исключает все idData, которые имеют идентификатор, отличный от 11 или 34
  2. HAVING исключает все idData, которые имеют только только a 34
  3. Оставшиеся записи (должны) удовлетворять всем ограничениям

Тестовые данные 

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
4
ответ дан 2 December 2019 в 21:22
поделиться

Думаю, это достигается за один проход через данные.

Это зависит от распределения данных, будет ли это предпочтительнее, чем выполнение двух отдельных поисков. 

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
1
ответ дан 2 December 2019 в 21:22
поделиться
Другие вопросы по тегам:

Похожие вопросы: