wybierz wiersze spełniające warunek dla grupy (bez tabeli tymczasowej)

Posiadanie tabeli z 3 kolumnami:

ID  category    flag
1       A       1
2       A       0
3       A       0
4       B       0
5       C       0

Chcę wybrać wszystkie wiersze, które mają flag = 1co najmniej raz na kategorię.

Oczekiwane rezultaty:

ID  category    flag
1       A       1
2       A       0
3       A       0

Można go rozwiązać za pomocą tabeli tymczasowej takiej jak ta:

select ID into #tempTable from someTable where flag = 1
select * from someTable join #tempTable on someTable.ID = #tempTable.ID

Ale wolałbym rozwiązanie z grupowaniem, z którym mam problem. Każda pomoc będzie mile widziana.

sql-server group-by

— Piotr Falkowski
źródło

Odpowiedzi:

GROUP BYnie może być użyte samodzielnie, ponieważ zwraca tylko 1 wiersz na grupę ( category).

Możesz użyć zapytania podrzędnego za pomocą flag = 1i INNER JOIN:

SELECT d1.ID, d1.category, d1.flag
FROM data d1
INNER JOIN (
    SELECT DISTINCT category FROM data WHERE flag = 1
) d2 
    ON d2.category = d1.category ;

Możesz użyć EXISTSklauzuli:

SELECT d.ID, d.category, d.flag
FROM data d
WHERE EXISTS (
    SELECT 1 FROM data WHERE flag = 1 AND category = d.category
) ;

Możesz użyć INklauzuli (chociaż EXISTSjest lepsza):

SELECT d.ID, d.category, d.flag
FROM data d
WHERE d.category IN (SELECT category FROM data WHERE flag = 1) ;

Możesz również użyć CROSS APPLYz zapytaniem podrzędnym na flag = 1:

SELECT d.ID, d.category, d.flag
FROM data d
CROSS APPLY (
    SELECT TOP (1) category 
    FROM data 
    WHERE flag = 1 AND category = d.category
) ca ;

DISTINCTnie są potrzebne, jeśli dla każdej kategorii może mieć tylko 1 wiersz flag = 1.

Wynik:

ID  category    flag
1       A       1
2       A       0
3       A       0

— Julien Vavasseur
źródło

DISTINCT nie jest konieczne dla predykatu IN. A jeśli tylko jeden wiersz na kategorię może mieć flagę 1, DISTINCT jest w ogóle niepotrzebny.

— Andriy M

@AndriyM poprawnie o INzapytanie. Ale OP ma „ Chcę wybrać wszystkie wiersze, które mają flagę = 1 przynajmniej raz na kategorię ”, co sprawia, że uważam, że DISTINCTjest to konieczne w innych zapytaniach.

— ypercubeᵀᴹ

A w CROSS APPLY, SELECT DISTINCT categoryprawdopodobnie powinien być bardziej wydajny, jeśli zostanie zastąpiony SELECT TOP (1) whatever. Byłby to skutecznie inny sposób napisania EXISTSpodzapytania.

— ypercubeᵀᴹ

@Andriy Dlatego dodałem wczoraj notatkę na podstawie pierwszego komentarza: niepotrzebne, jeśli jest tylko 1 wiersz z flagą = 1.

— Julien Vavasseur

Zakładając, że Flagjest to BITkolumna lub kolumna, INTktóra przyjmuje tylko 0i 1jako wartości, można to osiągnąć również za pomocą funkcji okienkowych. Na przykład:

DECLARE @Test TABLE
(
  ID INT
  , Category VARCHAR(1)
  , Flag BIT
);

INSERT INTO @Test (ID, Category, Flag)
VALUES (1, 'A', 1)
  , (2, 'A', 0)
  , (3, 'A', 0)
  , (4, 'B', 0)
  , (5, 'C', 0);

SELECT T.ID
  , T.Category
  , T.Flag
FROM (
  SELECT ID
    , Category
    , Flag
    , MAX(CAST(Flag AS TINYINT)) OVER(PARTITION BY Category) AS MaxFlag
  FROM @Test
  ) AS T
WHERE T.MaxFlag = 1;

To jest wynik:

ID Category Flag  
-- -------- ----- 
1  A        True  
2  A        False 
3  A        False

To będzie najwyższe Flagdla każdej kategorii w twojej tabeli, w twoim przypadku jest to prawdopodobnie tylko prawda / fałsz i wybierz tę, która ma true(1)tylko.

Konwersja na TINYINTjest potrzebna, ponieważ MAXnie przyjmuje BITargumentu.

— Evaldas Buinauskas
źródło