时间戳

首页 > 解决方案 > 连接后计算值的总和

mysql - 连接后计算值的总和

问题描述

我有这些表：

CREATE TABLE `items` (
  `itemId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `itemCategoryId` mediumint(8) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`itemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orders` (
  `orderId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `customerId` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `orders_items` (
  `orderItemId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `orderId` mediumint(8) unsigned NOT NULL,
  `itemId` mediumint(8) unsigned NOT NULL,
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`orderItemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `order_items_quantity` (
  `orderItemId` mediumint(8) unsigned NOT NULL,
  `size` tinyint(3) unsigned DEFAULT NULL,
  `quantity` smallint(5) unsigned NOT NULL,
  UNIQUE KEY `unique` (`orderItemId`,`size`,`quantity`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `items` (`itemId`, `itemCategoryId`, `name`)
VALUES
    (1, 1, 'Jeans Model A (Category Jeans)'),
    (2, 1, 'Jeans Model B (Category Jeans)'),
    (3, 2, 'T-Shirt Model A (Category T-Shirt)');

INSERT INTO `orders` (`orderId`, `customerId`)
VALUES
    (1, 1),
    (2, 2),
    (3, 1);

INSERT INTO `orders_items` (`orderItemId`, `orderId`, `itemId`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1),
    (4, 2, 2),
    (5, 3, 1),
    (6, 3, 3);

INSERT INTO `order_items_quantity` (`orderItemId`, `size`, `quantity`)
VALUES
    (1, 1, 2),
    (1, 2, 3),
    (2, 1, 3),
    (2, 2, 5),
    (3, 1, 1),
    (3, 2, 2),
    (4, 1, 1),
    (4, 2, 1),
    (5, 1, 4),
    (6, 1, 3);

我无法将 'order_items' 与 'order_items_quantity' 合并，因为我在第一个表中还有关于项目的其他字段，无论大小（例如，它们当时的价格）如果我只在一个表中重复它们会浪费磁盘空间.

我需要一个查询来获取每个客户对某个类别的任何项目的所有订购数量的总和，而不管大小。就像是：

customerId itemCategoryId total_quantity_ordered
1          1              17
2.         1              5
2.         2.             3

我写了这个查询：

SELECT total_quantities.total_quantity_ordered, orders.customerId, items.itemCategoryId FROM orders 
JOIN ( 
    SELECT orders_items.orderId, SUM(order_items_quantity.quantity) AS total_quantity_ordered 
    FROM orders_items 
    JOIN order_items_quantity ON order_items_quantity.orderItemId=orders_items.orderItemId 
    GROUP BY orders_items.orderId
) AS total_quantities ON total_quantities.orderId=orders.orderId 
JOIN orders_items ON orders_items.orderId=orders.orderId
JOIN items ON items.itemId=orders_items.itemId
GROUP BY orders.customerId, items.itemCategoryId

但它只选择包含该 categoryId 的每个客户的第一个订单。任何帮助表示赞赏。

SQL小提琴：https ://www.db-fiddle.com/f/bnxomXfobBN25nTJvASVdL/0

标签： mysqljoinsum

解决方案

您不需要子查询：

SELECT o.customerId, i.itemCategoryId  SUM(q.quantity) AS total_quantity_ordered
FROM orders o
INNER JOIN orders_items oi ON oi.orderId=o.orderId
JOIN order_items_quantity q ON oi.orderItemId=q.orderItemId
JOIN items i ON i.itemId=oi.itemId
GROUP BY o.customerId, i.itemCategoryId

推荐阅读