mysql - 无法执行 sql 查询

Question

我正在尝试执行此查询，但它显示错误

select 
    order_id, 
    product_id 
from (select 
        order_id, 
        product_id, 
        count(*) over(partition by order_id, product_id) as dc  
      from order_items
      ) 
where dc > 1  ;

我遇到的错误是

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by order_id, product_id) as dc from order_items) where dc > 1' at line 1

Answer 1

不幸的是，MySQL 仍然不支持窗口函数。因此 OVER 子句在 MySQL 中不起作用。

您可以使用子查询或使用用户( session )变量来模拟它。

从order_items中选择实际行的一种方法，其中相同的product_id在每个订单中出现多次

select i.*, 
       (
         select count(*) 
           from order_items
          where order_id = i.order_id 
            and product_id = i.product_id
       ) as dc  
  from order_items i
having dc > 1

或者加入

select i.*, dc
  from
(
  select order_id, product_id, count(*) dc
    from order_items
   group by order_id, product_id
  having dc > 1
) q join order_items i
    on q.order_id = i.order_id
   and q.product_id = i.product_id;

如果另一方面，您仅需要 order_id 和 Product_id 以及此类行的总计

select order_id, product_id, count(*) dc
  from order_items
 group by order_id, product_id
having dc > 1;

这里是SQLFiddle 演示