Блог

У меня есть 2 таблицы

 | Categories         |      | Products               |
:--------------------:      :------------------------:
| Id int             |      | Id int                 |
| Name nvarchar(max) |      | Name vnarchar(max)     |
                            | ApprovedForRelease bit |
                            | ApprovedForRecall bit  |
                            | CategoryId int         |
  

Мне нужно получить подсчеты для продуктов approvedForRealease, not approvedForRelease, ApprovedForRecall и not ApprovedForRecall для всех категорий

Что-то вроде этого

 | Category | #Released | #NotReleased | #Recalled | #NotRecalled |
:----------------------------------------------------------------:
| Arts     | 5         | 1            | 3         | 4            |
| Crafts   | 13        | 7            | 7         | 8            |
  

Мой запрос выглядит следующим образом

 SELECT

Category = cat.Name,
#Releases = Count(released.Id),
#NotReleased = Count(notReleased.Id),
#Recalled = Count(recalled.Id),
#NotRecalled = Count(notRecalled.Id),

-- Selected product ids
releasedIds = STRING_AGG(released.Id, ', '),
notReleasedIds = STRING_AGG(notReleased.Id, ', '),
recalledIds = STRING_AGG(recalled.Id, ', '),
notRecalledIds = STRING_AGG(notRecalled.Id, ', ')

FROM
Categories as cat

LEFT JOIN Products as released ON released.CategoryId = cat.Id AND released.ApprovedForRelease = 1
LEFT JOIN Products as notReleased ON released.CategoryId = cat.Id AND notReleased.ApprovedForRelease = 0
LEFT JOIN Products as recalled ON released.CategoryId = cat.Id AND recalled.ApprovedForRecall = 1
LEFT JOIN Products as notRecalled ON released.CategoryId = cat.Id AND notRecalled.ApprovedForRecall = 0

GROUP BY
cat.Name
  

Я заметил, что количество продуктов слишком велико, поэтому я добавил выбранные столбцы идентификаторов продуктов, чтобы проверить, что на самом деле соединяется, и заметил, что объединенные таблицы имеют одни и те же строки несколько раз

Пример результата, который я бы получил:

 | Category | #Released | #NotReleased | #Recalled | #NotRecalled | releasedIds | notReleasedIds | recalledIds | notRecalledIds |
:------------------------------------------------------------------------------------------------------------------------------:
| Arts     | 3         | 3            | 3         | 3            | 1, 2, 3     | 4, 5, 6        | 10, 10, 10  | 6, 6, 6        |
| Crafts   | 2         | 2            | 4         | 2            | 25, 26      | 96, 98         | 7, 8, 7, 8  | 9, 9           |
  

Может ли кто-нибудь объяснить мне, что происходит и почему некоторые продукты объединяются несколько раз?

И есть ли способ достичь желаемого результата без использования таких подзапросов, как:

 SELECT
Category = cat.Name,
#Releases = (SELECT COUNT (Id) FROM PRODUCTS WHERE CategoryId = cat.Id AND ApprovedForRelease = 1),
#NotReleased = (SELECT COUNT (Id) FROM PRODUCTS WHERE CategoryId = cat.Id AND ApprovedForRelease = 0),
#Recalled = (SELECT COUNT (Id) FROM PRODUCTS WHERE CategoryId = cat.Id AND ApprovedForRecall= 1),
#NotRecalled = (SELECT COUNT (Id) FROM PRODUCTS WHERE CategoryId = cat.Id AND ApprovedForRecall= 0)
FROM Categories
  

Используйте условную агрегацию:

 SELECT c.name as category,
       SUM(CASE WHEN p.ApprovedForRelease = 1 THEN 1 ELSE 0 END) as released,
       SUM(CASE WHEN p.ApprovedForRelease = 0 THEN 1 ELSE 0 END) as not_released,
       SUM(CASE WHEN p.ApprovedForRecall = 1 THEN 1 ELSE 0 END) as recalled,
       SUM(CASE WHEN p.ApprovedForRecall = 0 THEN 1 ELSE 0 END) as not_recalled
FROM Categories c LEFT JOIN
     Products p
     ON p.CategoryId = cat.Id 
 released.ApprovedForRelease = 1
GROUP BY c.name;