是否有任何语法错误，因为根据我的理解，查询是完美的

Question

如果有任何语法错误，请告诉我，因为根据我的理解，查询是完美的：

mysql> SELECT Cust_Id,Plan_Id FROM(SELECT Cust_Id,Plan_Id,ROW_NUMBER() OVER (PARTITION BY Cust_Id ORDER BY cnt DESC) AS RN FROM(SELECT Cust_Id,Plan_Id,COUNT(1) as cnt FROM customer GROUP BY (Cust_Id,Plan_Id))) WHERE RN =1;
ERROR 1064 (42000): 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 Cust_Id ORDER BY cnt DESC) AS RN FROM(SELECT Cust_Id,Plan_Id,COUNT'
at line 1

Answer 1

MYSQL不支持

ROW_NUMBER()。

您必须重写查询，如下所示

SELECT Cust_Id,Plan_Id 
FROM (
       SELECT Cust_Id,Plan_Id,
              @row_num := IF(@prev_value=T.Cust_Id,@row_num+1,1) AS RN,
              @prev_value := T.Cust_Id
       FROM (
           SELECT Cust_Id,Plan_Id,COUNT(1) as cnt 
           FROM customer 
           GROUP BY (Cust_Id,Plan_Id)
            ) T,
           (SELECT @row_num := 1) x,
           (SELECT @prev_value := 0) y
      )S WHERE RN =1;