среда, 20 марта 2013 г.

Как вывести все возможные подитоги (Cube)


-- Как вывести все возможные подитоги (Cube)

 

-- Вычислим максимальные цены для:

-- Сегментов сочетания товарной линейки, ценовой категории (ProductLine, Class)

SELECT

    Productline

    ,Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

GROUP BY ProductLine, Class

    UNION ALL

-- Товарных линеек (ProductLine)

SELECT

    Productline

    ,NULL AS Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

GROUP BY ProductLine

    UNION ALL

-- Ценовых категорий (Class)

SELECT

    NULL AS Productline

    ,Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

GROUP BY Class

    UNION ALL

-- Всех товаров

SELECT

    NULL AS Productline

    ,NULL AS Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

-- Отсортируем результат

ORDER BY ProductLine, Class;

 

 

-- А теперь более изящно

 

SELECT

    Productline

    ,Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

GROUP BY ProductLine, Class WITH CUBE;

 

-- Если SQL Server >= 2008

SELECT

    Productline

    ,Class

    ,MAX(ListPrice) AS MaxListPrice

FROM AdventureWorks2012.Production.Product

WHERE Class IS NOT NULL AND ProductLine IS NOT NULL

GROUP BY CUBE (ProductLine, Class);