mysql - 使用 MySQL 从两个 'items' 表(均具有不同的值)中获取数据，使用第三个 'itemlist' 表

Question

首先，这是一篇很长的文章。感谢您花时间仔细阅读! :)

我有如下三张表(抱歉发了这么长的帖子，但最好清楚一点...)

-- 表 itemdata 的表结构

CREATE TABLE IF NOT EXISTS `itemdata` (
  `itemID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand`  enum('dnc','fbiz','jbs','wspirit','aspirit','grace','legend','stencil','gfl','bocini','beseen','ljapp','lwreid') NOT NULL,
  `category` enum('shirts','headwear','winter','sports','bags','misc') NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(20) NOT NULL,
  `colourway` varchar(255) NOT NULL DEFAULT 'Black',
  `gender` enum('mens','ladies','kids','unisex') NOT NULL,
  `sizerange` varchar(255) NOT NULL DEFAULT 'S|M|L|2XL|3XL|4XL|5XL',
  `discontinued` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemID`),
  KEY `name` (`name`,`code`,`gender`),
  KEY `brand` (`brand`),
  KEY `category` (`category`),
  KEY `discontinued` (`discontinued`),
  KEY `colourway` (`colourway`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=313 ;

-- 表 itemdata 的示例数据

INSERT INTO `itemdata` (`itemID`, `brand`, `category`, `name`, `code`, `colourway`, `gender`, `sizerange`, `discontinued`) VALUES (1, 'jbs', 'shirts', 'Fitted Tee', '1LHT1', 'Blue', 'ladies', '8|10|12|14|16|18|20', 0);

-- 表 orderitems 的表结构

CREATE TABLE IF NOT EXISTS `orderitems` (
  `orderID` int(10) unsigned NOT NULL,
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  KEY `orderid` (`orderID`,`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 表 orderitems 的示例数据

INSERT INTO `orderitems` (`orderID`, `itemID`, `size`, `qty`) VALUES (1, 123, 'S', 1);

-- 表 stockitems 的表结构

CREATE TABLE IF NOT EXISTS `stockitems` (
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(11) NOT NULL,
  KEY `size` (`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 表 stockitems 的示例数据

INSERT INTO `stockitems` (`itemID`, `size`, `qty`) VALUES (124, 'S', 5);

两个项目表:stockitems 和 orderitems
和一个列表:itemdata

现在，我要做的是运行一个查询，该查询将查看两个项目表(stockitems & orderitems 并返回来自 itemdata 的信息 加上每个 itemID、颜色和尺码的库存和订购总量。

编辑:我忘了补充一点，这两个项目表的组织方式有些不同。stockitems 表永远不会有重复的行，而 orderitems 可能有许多相同的行，所有这些“重复”行将完全相同，除了qty 字段可能不同也可能不同。我需要获取 orderitems 表的 qty 字段的 SUM，按大小和 itemID 分组。

理想情况下，我希望:
itemID, brand, category, name, code, colourway, 性别, 停产, 尺寸, 库存数量, 数量订单

我已经尝试了 2 个多小时的不同查询，并试图找到一篇关于如何完成我想做的事情的文章，但收效甚微。

我唯一设法得到所需的数据，但它只返回存在于 orderitems 和 stockitems 中的数据，但我需要它甚至返回数据如果商品只有库存或仅订购。

下面是那个查询:

SELECT id.itemID, id.category, id.brand, id.code, id.gender, id.name, id.colourway, 
  id.discontinued, stk.size, stk.qty, ord.size, ord.qty
FROM itemdata id 
  JOIN stockitems stk ON (stk.itemID = id.itemID)
  JOIN orderitems ord ON (ord.itemID = id.itemID)
WHERE stk.size = ord.size

有什么想法吗？

Answer 1

回答你真正要求的，这就是如何

return the information from itemdata plus the total quantity 
both in stock and ordered for each itemID, colourway & size

首先，您没有在 orderinfo 和 stockinfo 表中存储任何关于颜色的信息，因此无法根据颜色获取数量。所以让我们忽略颜色。

要根据 itemID 和大小从 orderinfo 和 stockinfo 中获取数量，而不确定在任何这些表中是否存在给定 id 和大小的行，您需要 FULL OUTER JOIN，这在 mysql 中不受支持，但您可以实现使用两个镜像 LEFT JOIN 和 UNION 的结果相同。对于 orderinfo 表，您需要 GROUP BY itemID 和 size 以对每个组内的数量求和。加入两个表后，您可以在 itemID 上使用 itemdata 表对结果进行内部 JOIN 以获取每个项目的信息。您实际上不需要触摸 sizerange，因为如果尺寸与可能的 sizerange 匹配，我们不需要检查此查询。所以最终的查询应该是这样的:

SELECT oisi.size,oisi.oqty,oisi.sqty,id.* FROM
itemdata id
JOIN   ( SELECT oi.itemID,oi.size,oi.oqty,COALESCE(si.qty,0) as sqty
         FROM ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         LEFT JOIN stockitems si 
         ON si.itemID=oi.itemID AND si.size=oi.size
         UNION 
         SELECT si.itemID,si.size,COALESCE(oi.oqty,0) as oqty,si.qty as sqty 
         FROM stockitems si
         LEFT JOIN ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         ON si.itemID=oi.itemID AND si.size=oi.size ) oisi
ON id.itemID=oisi.itemID

您可以将结果集限制为类别、品牌、性别等的任意组合，在末尾添加 WHERE 并使用 ORDER BY 按您希望的任何列对其进行排序。

我也建议你这样做:

ALTER TABLE orderitems ADD INDEX item_size( itemID, size ) ;

因为我们将这些列用于 GROUP BY。