MySql:在查询中初始化 mySql 变量 [英] MySql : Initialize mySql variable inside a query

问题描述

这是我的 MySql 查询执行的顺序:

This is the sequence of my MySql Query executions:

查询 1 : SET @channel_rank = 0;

查询 2:

  SELECT time_of_day, @channel_rank := IF(
        @current_channel = channel,
        1,
        @channel_rank + 1
      ) AS channel_rank , 
      @current_channel := channel AS channel,Views
    FROM
    (
    SELECT @channel_rank = 0,time_of_day,channel, SUM(Views) AS 'Views'
      FROM access_logs_meaningful_optimized
      WHERE `time_of_day` = 0
      AND playing_date = '2016-10-26' GROUP BY channel
      ORDER BY SUM(views) DESC
      LIMIT 5
     ) xx; 

示例结果:

time_of_day  channel_rank  channel                Views   
-----------  ------------  ---------------------  --------
          0             1  Tolo                   1291    
          0             2  Tolo News              855     
          0             3  Samaa News             805     
          0             4  Ary Digital            695     
          0             5  Dunya News             653     

在这里，我必须先执行 SET @channel_rank = 0; 才能将变量 (@channel_rank) 赋值为 0.我的问题是 如何，在 query 2 中，我可以将变量 (@channel_rank) 分配给 0 最初使 第二个查询独立于第一个查询.

Over here I have to execute SET @channel_rank = 0; first in order to assign the variable (@channel_rank) to 0. My question is HOW, inside the query 2 can I assign the the variable (@channel_rank) to 0 initially making the second query independent of the first one.

推荐答案

您不必在子查询中初始化变量.您可以改为使用 CROSS JOIN 初始化变量:

You don't have to initialize the variable in the subquery. You can instead initialize the variable using CROSS JOIN:

SELECT time_of_day, 
       @channel_rank := IF(@current_channel = channel, 1, 
                             @channel_rank + 1) AS channel_rank, 
       @current_channel := channel AS channel,Views
FROM
(
   SELECT time_of_day,channel, SUM(Views) AS 'Views'
   FROM access_logs_meaningful_optimized
   WHERE `time_of_day` = 0
   AND playing_date = '2016-10-26' 
   GROUP BY channel
   ORDER BY SUM(views) DESC
   LIMIT 5
) AS xx
CROSS JOIN (SELECT @channel_rank := 0) var

这篇关于MySql:在查询中初始化 mySql 变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！