代码之家 › 专栏 › 技术社区 › AHS441

在MySQL中连接三个表,每个id获取一行(第一个表中每个实体一行)

spring-data-jpa join sql mysql

0

AHS441 · 技术社区 · 7 年前

我有三个产品表,产品配置(如iPhone x 64黑色、iPhone x 64红色)和产品库存;我想选择所有产品并按价格(在配置表中)和可用性(在库存表中)排序,但每个产品只能得到一行;我该怎么做?

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

我有以下结果;但我希望每个配置有一个最低价格和最高数量的产品

2 回复 | 直到 7 年前

1

0

Hello World 7 年前

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

2

0

FanoFN 7 年前

试试这个,看看是否有效:

SELECT *,MIN(price),MAX(quantity) 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 INNER JOIN product_configs ON product_inventories.config_id = product_configs.config_id ORDER BY product_inventories.quantity DESC LIMIT 1 ) GROUP BY sku ORDER BY product_configs.price ASC, inventories.quantity DESC;

请注意,我仍然保留 SELECT * 并补充说 min(price),max(quantity)