-- Сколько в каждой категории
(CategoryName)
-- товаров в продаже
(Discontinued=0) и склолько снято с продаж (Discontinued=1)?
--
-- Подзапросами
SELECT CategoryName,
(
SELECT
Count(*)
From
Products
WHERE
Discontinued=0
AND
CategoryID = Categories.CategoryID
)
AS Cont
,
(
SELECT
Count(*)
From
Products
WHERE
Discontinued=1
AND
CategoryID = Categories.CategoryID
)
AS Disc
FROM Categories
--
|
-- Джойнами
SELECT CategoryName, Count(DISTINCT P1.ProductID) AS Con , Count(DISTINCT P2.ProductID) As Discont
FROM Categories C LEFT JOIN Products P1
ON C.CategoryID=P1.CategoryID
AND P1.Discontinued=0
LEFT JOIN Products P2
ON C.CategoryID=P2.CategoryID
AND P2.Discontinued=1
GROUP BY CategoryName
--
|
-- APPLY Джойнами
SELECT CategoryName, P1.Discon, P2.Cont
FROM Categories C CROSS APPLY (
SELECT Count(*) AS Discon
FROM Products
WHERE Discontinued=1
AND CategoryID=C.CategoryID
) P1
CROSS APPLY (
SELECT Count(*) AS Cont
FROM Products
WHERE Discontinued=0
AND CategoryID=C.CategoryID
) P2
Order by
C.CategoryName
|
Friday, April 19, 2013
Одна задачка при помощи Subquery, JOIN и APPLY
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment