mysql - 使用 DENSE_RANK() MySQL 缺少右括号

Question

我不确定我的语法哪里错了。我需要根据 invoice_total

显示顶级供应商

select *
from (
        select vendor_id, invoice_total,
        dense_rank () over(partition by vendor_id order by invoice_total asc)
          as ranking
        from invoices) a1

Answer 1

MySQL 仅在版本 8+ 中支持 dense_rank()。您始终可以使用相关子查询:

select i.*
from invoices i
where i.invoice_total = (select max(i2.invoice_total)
                         from invoices i2
                         where i2.vendor_id = i.vendor_id
                        );

这假设“顶级供应商”指的是最大总数，这与您的 SQL 相反。

还有其他表达方式。我也喜欢在 MySQL 中使用元组:

select i.*
from invoices i
where (i.vendor_id, i.invoice_total) in
          (select i2.vendor_id, max(i2.invoice_total)
           from invoices i2
           group by i2.vendor_id
          );