CTE с группой Возвращая те же результаты, что и базовый запрос

Я пытаюсь выбрать количество обзоров продуктов по рейтингу, где рейтинг может быть от 0 до 5.

Следующий базовый выбор работает, но не дает подсчет оценок, которых нет в базовой таблице.

SELECT Rating, COUNT(*)  AS 'Reviews' FROM  ProductReviews 
WHERE   ProductID = 'product1' 
GROUP BY Rating

Я попытался использовать CTE для создания отсутствующих результатов, присоединенных к таблице Reviews с помощью внешнего соединения, как только я попытаюсь включить выражение «group by», результаты возвращаются, чтобы соответствовать результатам из базового запроса.

(Я проверил, что CTE действительно генерирует полный диапазон требуемых значений).

BEGIN

DECLARE @START AS INT = 0;
DECLARE @END AS INT = 5;

WITH CTE_Ratings AS
(
    SELECT @START as cte_rating
    UNION ALL
    SELECT 1 + cte_rating
    FROM CTE_Ratings
    WHERE cte_rating < @END
)


SELECT
    cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(*), 0) AS 'ReviewCount'
FROM CTE_Ratings
LEFT OUTER JOIN Reviews ON Reviews.Rating = cte_rating
WHERE ProductReviews.ProductID = 'product1'
    AND cte_rating BETWEEN @START AND @END
    GROUP BY cte_rating
END

(Я также попытался создать временную таблицу, содержащую необходимые значения, объединенные с таблицей Reviews, с идентичными результатами).

В случае обоих вышеуказанных запросов результаты следующие:

Rating  Reviews
0       1
3       3
4       9
5       47

Принимая во внимание, что я пытаюсь получить те же данные:

Rating  Reviews
0       1
1       0
2       0
3       3
4       9
5       47

Может ли кто-нибудь предложить, когда добавление агрегатной функции Group By приводит к сбою запроса или как его можно улучшить?


sql sql-server common-table-expression
person user1999544    schedule 22.01.2013    source источник

Ответы (1)


arrow_upward
2
arrow_downward

WHERE изменяет OUTER JOIN на INNER JOIN, потому что вы фильтруете необязательные строки из внешней таблицы.
Итак, переместите фильтр внешней таблицы в JOIN.

Кроме того, вы уже выполняете фильтрацию между началом и концом в CTE.

WITH ...
-- CTE here
SELECT
    C.cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(PR.Rating), 0) AS 'ReviewCount'
FROM
   CTE_Ratings C
   LEFT OUTER JOIN
   ProductReviews PR ON C.cte_rating= PR.Rating AND PR.ProductID = 'product1'
GROUP BY
   C.cte_rating

Более ясно, вы на самом деле делаете это

WITH ...
-- CTE here
SELECT
    C.cte_rating AS 'ReviewRating'
    , ISNULL(COUNT(PR.Rating), 0) AS 'ReviewCount'
FROM
   CTE_Ratings C
   LEFT OUTER JOIN
   (
      SELECT PR.Rating
      FROM ProductReviews
      WHERE ProductID = 'product1'
   ) PR ON C.cte_rating= PR.Rating
GROUP BY
   C.cte_rating
person gbn    schedule 22.01.2013
comment
Спасибо за это, понимание того, что пошло не так, сводило меня с ума - единственное, что когда я пытаюсь сделать выше, отсутствующие рейтинги возвращают число 1, а не ноль, как я ожидал от функции ISNULL. - person user1999544; 22.01.2013
comment
@ user1999544: Вам просто нужно сделать COUNT(PR.something) вместо COUNT(*). - person Andriy M; 22.01.2013
comment
@AndriyM: ой. Исправлено. Спасибо - person gbn; 22.01.2013