mysql - Group By 和 Get Count of 1 to many 表

Question

我有一个看起来像这样的表

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

和另一个存储所有购买细节的表(例如订单项)

CREATE TABLE `purchase_items` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `purchaseId` INT(10) NOT NULL,
    `itemId` INT(10) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`purchaseId`) REFERENCES purchases(id)
)

我通过这种方式获得任何给定日期的总购买量

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

这给了我这样的结果

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

我将如何执行查询以使其返回类似这样的内容

date        totalPurchases  totalItems
11-18-2010  5               20
11-19-2010  10              30
11-20-2010  10              25

所以我仍将按日期分组，但我需要对“purchase_items”执行连接并获取链接到的项目总数

• 2010 年 11 月 18 日购买 5 次
• 2010 年 11 月 19 日购买了 10 笔
• 2010 年 11 月 20 日购买了 10 笔

Answer 1

使用:

  SELECT DATE_FORMAT(p.purchasedate, '%m-%d-%Y'),
         COUNT(p.id) AS totalPurchases,
         COUNT(pi.itemid) AS totalItems
    FROM PURCHASES p
    JOIN PURCHASE_ITEMS pi ON pi.purchaseid = p.id
GROUP BY DATE_FORMAT(p.purchasedate, '%m-%d-%Y')

如果可能与 PURCHASE_ITEMS 表无关，请使用:

   SELECT DATE_FORMAT(p.purchasedate, '%m-%d-%Y'),
          COUNT(p.id) AS totalPurchases,
          COUNT(pi.itemid) AS totalItems
     FROM PURCHASES p
LEFT JOIN PURCHASE_ITEMS pi ON pi.purchaseid = p.id
 GROUP BY DATE_FORMAT(p.purchasedate, '%m-%d-%Y')