问题描述:

I've written this bit of SQL: it returns the Items sold past the month of November. However, I want to show all items that were sold and just to stick a 0 next to the one where it didn't sell in that month.

Would appreciate some guidance:

SELECT ItemID, SUM(Quantity)

FROM orderitems

WHERE OrderNumber IN(

SELECT OrderNumber

FROM `order`

WHERE OrderDate > "2015-11-31") GROUP BY ItemID;

网友答案:

Try this:

SELECT ItemID, 
       COALESCE(SUM(CASE 
                      WHEN o2.OrderDate > '2015-11-31' 
                      THEN Quantity 
                    END), 0) AS Sum_Of_Quantity 
FROM orderitems AS o1
LEFT JOIN (SELECT OrderNumber, OrderDate
           FROM `order`
           WHERE OrderDate > '2015-11-31') AS o2
   ON o1.OrderNumber = o2.OrderNumber
GROUP BY ItemID;
网友答案:

One more table is needed here: the one with full list of items whether ever ordered or not.

Then it would be:

SELECT
    FullListOfItemsOnSale.ItemID,
    IFNULL(OrdetedItems.Quantity, 0) AS Quantity
FROM FullListOfItemsOnSale
LEFT JOIN (
    SELECT ItemID, SUM(i.Quantity) AS Quantity
    FROM orderitems i
    INNER JOIN `order` o ON o.OrderNumber = i.OrderNumber
                        AND o.OrderDate > "2015-11-31"
) OrdetedItems ON OrdetedItems.ItemID = FullListOfItemsOnSale.ItemID
网友答案:
SELECT ItemID, 
       ISNULL(SUM(CASE WHEN c2.OrderDate > '2015-11-31' THEN Quantity END), 0) Sum_Of_Quantity 
FROM orderitems AS o1
LEFT JOIN (SELECT OrderNumber, OrderDate
           FROM `order`
           WHERE OrderDate > '2015-11-31') AS o2
   ON o1.OrderNumber = o2.OrderNumber
GROUP BY ItemID;
相关阅读:
Top