gpt4 book ai didi

mysql - 连接 MySQL 中的三个表并获取每个 id 一行(第一个表中每个实体一行)

转载 作者:行者123 更新时间:2023-11-29 16:27:04 26 4
gpt4 key购买 nike

我有三个产品表、产品配置(例如 iPhone x 64 黑色、iPhone x 64 红色)和产品库存;我想选择所有产品并按价格(在配置表中)和可用性(在库存表中)排序,但每个产品只获取一行;我怎么能做到这一点?下面是我的mysql表结构

CREATE TABLE `product_inventories` (
`inventory_id` bigint(20) NOT NULL,
`quantity` int(11) NOT NULL,
`warehouse_id` bigint(20) DEFAULT NULL,
`config_id` bigint(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `product_configs` (
`config_id` bigint(20) NOT NULL,
`product_id` bigint(20) DEFAULT NULL,
`price` decimal(12,3) DEFAULT NULL,
`discount_percent` int(11) DEFAULT NULL,
`sku` varchar(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

CREATE TABLE `products` (
`id` bigint(20) NOT NULL,
`title` varchar(255) DEFAULT NULL,
`category_id` bigint(20) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

我尝试了这个查询:

SELECT * 
FROM products
LEFT JOIN product_configs ON products.id = product_configs.product_id
LEFT JOIN product_inventories inventories ON inventories.inventory_id =
(SELECT product_inventories.inventory_id from product_inventories
WHERE product_inventories.config_id = product_configs.config_id
ORDER by product_inventories.quantity DESC LIMIT 1 )
ORDER BY product_configs.price ASC, inventories.quantity DESC

编辑:使用此查询:

SELECT *
FROM products
LEFT JOIN product_configs ON products.id = product_configs.product_id
LEFT JOIN product_inventories inventories ON inventories.inventory_id =
(SELECT product_inventories.inventory_id from product_inventories
WHERE product_inventories.config_idd = product_configs.config_id
ORDER by product_inventories.quantity DESC LIMIT 1 )

ORDER BY product_configs.price is null, product_configs.price ASC, inventories.quantity DESC

我有以下结果;但我想要每个配置一个产品,具有最低价格和最大数量 query result

最佳答案

试试这个:

SELECT 
...,
SUM(quantity) AS total_quantity
FROM products AS p
LEFT JOIN product_configs AS pc ON pc.product_id = p.id
LEFT JOIN product_inventories AS pi ON pi.config_id = pc.config_id
GROUP BY p.id
ORDER BY pc.price ASC, pi.quantity DESC

关于mysql - 连接 MySQL 中的三个表并获取每个 id 一行(第一个表中每个实体一行),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54182420/

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