mysql - 如何选择总行数作为列

Question

我的第一个查询

SELECT
    id,
    year_,
    month_
FROM
    (SELECT
         tp.id,
         YEAR(FROM_UNIXTIME(tp.visited_date)) as year_, 
         MONTH(FROM_UNIXTIME(tp.visited_date)) as month_,
         @rn := IF(@prev = CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)),MONTH(FROM_UNIXTIME(tp.visited_date))), @rn + 1, 1) AS rn,
         @prev := CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)),MONTH(FROM_UNIXTIME(tp.visited_date)))
     FROM 
         tr_place tp
     JOIN 
         (SELECT @prev := NULL, @rn := 0) AS vars
     ORDER BY 
         YEAR(FROM_UNIXTIME(tp.visited_date)) DESC, 
         MONTH(FROM_UNIXTIME(tp.visited_date)) DESC) AS T1
WHERE 
    rn < 3;

将返回数据集This is the result which I got from the query

其中的子查询

SELECT
    tp.id,
    YEAR(FROM_UNIXTIME(tp.visited_date)) as year_, 
    MONTH(FROM_UNIXTIME(tp.visited_date)) as month_,
    @rn := IF(@prev = CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)),MONTH(FROM_UNIXTIME(tp.visited_date))), @rn + 1, 1) AS rn,
    @prev := CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)),MONTH(FROM_UNIXTIME(tp.visited_date)))
 FROM 
     tr_place tp
 JOIN 
     (SELECT @prev := NULL, @rn := 0) AS vars
 ORDER BY 
     YEAR(FROM_UNIXTIME(tp.visited_date)) DESC,
     MONTH(FROM_UNIXTIME(tp.visited_date)) DESC;

返回数据The sub query will returns this data

我需要子查询最大的 rn 作为第一个查询中的列。

我怎样才能实现这一目标？

Answer 1

您可能遇到变量赋值问题。您不应在 select 中的多个表达式中引用变量。因此，编写第一个查询的正确方法是:

SELECT id, year_, month_
FROM (SELECT tp.id,
             YEAR(FROM_UNIXTIME(tp.visited_date)) as year_, 
             MONTH(FROM_UNIXTIME(tp.visited_date)) as month_,
             (@rn := IF (@prev = CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)), MONTH(FROM_UNIXTIME(tp.visited_date))), @rn,
                         if(@prev := CONCAT(YEAR(FROM_UNIXTIME(tp.visited_date)), MONTH(FROM_UNIXTIME(tp.visited_date))), 1, 1
                        )
             ) as rn
      FROM tr_place tp JOIN
           (SELECT @prev := NULL, @rn := 0) AS vars
      ORDER BY YEAR(FROM_UNIXTIME(tp.visited_date)) DESC, 
               MONTH(FROM_UNIXTIME(tp.visited_date)) DESC
     ) t1
WHERE rn < 3;

请注意，变量赋值全部在单个表达式中。

这可能会解决您的问题。