时间戳

首页 > 解决方案 > sql max 函数出错：关键字“select”附近的语法不正确

mysql - sql max 函数出错：关键字“select”附近的语法不正确

问题描述

我有下一个 sql 代码，您可以在下面看到。它的工作原理除了最后一行的 where 条件。

    select m.model,m.price from (
    select product.model, pc.price
    from product
    join pc 
    on product.model=pc.model
    union
    select product.model, laptop.price
    from product
    join laptop 
    on product.model=laptop.model
union
    select product.model, printer.price
    from product
    join printer 
    on product.model=printer.model
    ) m
    where m.price = select max(m.price) from m

我有下一个错误：关键字“select”附近的语法不正确。如果我把最后一行写成：where m.price = m.price它也可以

标签： mysqlsql

解决方案

如果您正在寻找价格最高的型号：

select m.model, m.price
from (select p.model, pc.price
      from product p join
           pc 
           on p.model = pc.model
      union all
      select p.model, laptop.price
      from product join
           laptop 
           on p.model = laptop.model
      union all
      select p.model, printer.price
      from product p join
           printer 
           on p.model = printer.model
    ) m
order by m.price desc
limit 1;

但是，这过于复杂，因为join不需要 s：

select m.model, m.price
from (select pc.model, pc.price
      from pc 
      union all
      select laptop.model, laptop.price
      from laptop 
      union all
      select printer.model, printer.price
      from printer 
    ) m
order by m.price desc
limit 1;

推荐阅读