选择多个项目的计数,按日期分组

以下是对我有效的方法(使用MySQL):

SELECT o.date, COUNT(if(oi.type='tshirts', 1, NULL)) as tshirts, COUNT(if(oi.type='mugs', 1, NULL)) as mugs
FROM order_item oi LEFT JOIN order o ON oi.order_id = o.id
GROUP BY o.date

不应使用逗号语法进行连接。使用ISO语法,它使用可以缩短为JOIN的INNER JOIN关键字。

Select O.Date
    , Sum( Case When OI.Type = 'tshirts' Then 1 Else 0 End ) As tshirts
    , Sum( Case When OI.Type = 'dvds' Then 1 Else 0 End ) As dvds
    , Sum( Case When OI.Type = 'mugs' Then 1 Else 0 End ) As mugs
    , Sum( Case When OI.Type = 'cds' Then 1 Else 0 End ) As cds
From Order As O
    Join Order_Item As OI
        On OI.order_id = O.id
Where OI.type In('tshirts','dvds','mugs','cds')
Group By O.Date

听起来像是SQL Server的例子 PIVOT 命令:

http://msdn.microsoft.com/en-us/library/ms177410.aspx

以下是我最终使用的查询:

SELECT DATE(order.date) AS day,
     (SELECT COUNT(order.date)
     FROM order, order_item
     WHERE DATE(order.date) = day AND
        order.id = order_item.order_id AND
        order_item.type = 'tshirt') AS tshirt,
    (SELECT COUNT(order.date)
     FROM order, order_item
     WHERE DATE(order.date) = day AND
        order.id = order_item.order_id AND
        order_item.type = 'mugs') AS mugs,
    etc.
FROM order
GROUP BY DATE(order.date)

它的速度非常慢,但它确实起到了作用。

它不会给你你想要的东西

SELECT DATE(order.date), COUNT(order_item.type) as count , order_item.type as type
FROM order, order_item
WHERE order.id = order_item.order_id AND order_item.type = 'tshirts'
GROUP BY DATE(order.date), order_item.type

Date         type      count             
07/07/2010   tshirts    3       
07/07/2010   dvds       6       
07/07/2010   mugs       2      
07/07/2010   cds        2
10/07/2010   tshirts    4       
10/07/2010   dvds       9       
10/07/2010   mugs       3      
10/07/2010   cds        1
13/07/2010   tshirts    1       
13/07/2010   dvds       2       
13/07/2010   mugs       1      
13/07/2010   cds        9

我知道的两个缺点是

2. 不算零