mysql - SQL ROW_num 函数

Question

我正在运行以下查询并收到以下错误 -

MySQL Database Error: 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 STUDY_SITE_ID ORDER BY VISIT_START_DATE DESC) AS cnt FROM DESIRE' at line 1 3 28

查询 -

SELECT *, ROW_NUMBER() OVER (PARTITION BY STUDY_SITE_ID ORDER BY VISIT_START_DATE DESC) AS cnt 
 FROM TABLE_a

Answer 1

您可以使用变量。 。 。但非常重要的是，赋值必须在单个表达式内。所以:

SELECT a.*,
       (@rn := if(@ss = a.STUDY_SITE_ID, @rn + 1,
                  if(@ss := a.STUDY_SITE_ID, 1, 1)
                 )
       ) as cnt
FROM (SELECT a.*
      FROM TABLE_a a
      ORDER BY a.STUDY_SITE_ID, a.VISIT_START_DATE DESC
     ) a CROSS JOIN
     (SELECT @ss := -1, @rn := 0) params