MYSQL 获取最低值的记录 | View 的 SELECT 在 FROM 子句中包含一个子查询

Question

我一直在研究这个查询，它让我抓狂。

我有一个产品表和一个包含子产品的表。简而言之，我想创建一个包含产品数据和子产品的最低(折扣)价格的 View 。 (想想一件衬衫，有几个子产品(颜色/尺码)等)

其次，我想在 VIEW 中使用这个查询，这部分让我抓狂。

我现在的查询:

  SELECT m.* from product_items m join
  (select product_id, min(price_discount) md 
       from product_items group by product_id) mm
  on m.product_id=mm.product_id and m.price_discount=md

此查询有效，我得到了很好的结果。但现在我想创建一个 View (vw_product_lowest)。然后是错误:ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

谁能帮我将该查询转换为兼容的 VIEW 查询？谢谢!

Answer 1

您有几个选择:

1. 将子查询放入一个 View (可能会很慢，因为生成的 View 没有索引来执行后续连接):

   CREATE VIEW mm AS
     SELECT   product_id, MIN(price_discount) price_discount
     FROM     product_items
     GROUP BY product_id
   ;
   
   CREATE VIEW my_view AS
     SELECT * FROM product_items m NATURAL JOIN mm
   ;
   

2. 使用 correlated subquery (也可能很慢，因为必须为表中的每条记录评估子查询 - 使用 (product_id, price_discount) 上的复合索引将获得最佳性能):

   CREATE VIEW my_view AS
     SELECT * FROM product_items m WHERE price_discount = (
       SELECT MIN(mm.price_discount)
       FROM   product_items mm
       WHERE  mm.product_id = m.product_id
     )
   ;
   

3. 优化相关子查询 using the EXISTS strategy (还将受益于 (product_id, price_discount) 的复合索引):

   CREATE VIEW my_view AS
     SELECT * FROM product_items m WHERE NOT EXISTS (
       SELECT 1
       FROM   product_items mm
       WHERE  mm.product_id     = m.product_id
          AND mm.price_discount < m.price_discount
       LIMIT  1
     )
   ;