gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-30 23:38:00 24 4
gpt4 key购买 nike

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

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

-- 表 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);

两个项目表:stockitemsorderitems
和一个列表:itemdata

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

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

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

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

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

下面是那个查询:

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

有什么想法吗?

最佳答案

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

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。

关于mysql - 使用 MySQL 从两个 'items' 表(均具有不同的值)中获取数据,使用第三个 'itemlist' 表,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5964138/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com