SQL - 集团通过问题

Question

我有这个表看起来像这样

CREATE TABLE `purchases` (
    `id` INT(10) NOT NULL AUTO_INCREMENT, 
    `totalAmount` INT(10) NOT NULL DEFAULT '0', 
    `storeId` INT(10) NOT NULL DEFAULT '0', 
    `purchaseDate` DATETIME NOT NULL, 
    PRIMARY KEY (`id`) 
) 

我要统计所有在某一天的购买，所以我认为这是否

SELECT DATE_FORMAT(purchaseDate, '%m-%d-%Y') AS date, COUNT(id) AS totalPurchases FROM purchases GROUP BY DATE(purchaseDate) 

这条SQL语句返回我是这样的

date  totalPurchases 
11-18-2010 5 
11-19-2010 10 
11-20-2010 10 

由于每次进货被绑定到一家商店，我在想，如果有一个查询，无线网络将3列添加到该结果集与每个商店发生的购买计数给我这样的事情

date  totalPurchases  store1 store2 store3 
11-18-2010 5     2   1   2 
11-19-2010 10     4   1   5 
11-20-2010 10     3   4   3 

在此先感谢！

Answer 1

SELECT DATE_FORMAT(purchaseDate, '%m-%d-%Y') AS date, 
     COUNT(*) AS totalPurchases, 
     SUM(IF(storeId = 1, 1, 0)) AS store1, 
     SUM(IF(storeId = 2, 1, 0)) AS store2, 
     SUM(IF(storeId = 3, 1, 0)) AS store3 
    FROM purchases 
GROUP BY DATE(purchaseDate) 

Answer 2

也许你可以使用汇总此，检查出的文档here