mysq中自定义变量在查询中应用，及 :=和=的区别

第一部分：自定义变量在查询中基础用法：

官方文档关于自定义变量说明：https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

 

CREATE TABLE `tp2`  (
  `id` int(0) NOT NULL,
  `num` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tp2
-- ----------------------------
INSERT INTO `tp2` VALUES (1, 1);
INSERT INTO `tp2` VALUES (2, 1);
INSERT INTO `tp2` VALUES (3, 1);
INSERT INTO `tp2` VALUES (4, 2);
INSERT INTO `tp2` VALUES (5, 1);
INSERT INTO `tp2` VALUES (6, 4);
INSERT INTO `tp2` VALUES (7, 4);
INSERT INTO `tp2` VALUES (8, 4);
INSERT INTO `tp2` VALUES (9, 3);
INSERT INTO `tp2` VALUES (10, 3);

SET FOREIGN_KEY_CHECKS = 1;

select distinct t1.num as result from tp2 t1
inner join  tp2 t2 on t1.num = t2.num and t1.id <> t2.id-1
inner join  tp2 t3 on t2.num = t3.num and t2.id <> t3.id-1

-- 用if(@num is null,@num = 1,@num := @num +1),由于@num生命周期是整个会话
-- 下次执行将会从结果开始
select @num := @num+1,id ,num from tp2,(select @num :=0) b

-- 不是id不等于,而是连着相等,意味着id连续 0.038
select distinct t1.num as result from tp2 t1
inner join  tp2 t2 on t1.num = t2.num and t1.id = t2.id-1
inner join  tp2 t3 on t2.num = t3.num and t2.id = t3.id-1

-- 并且这里可以优化，将后面id放在where里面，0.027

select distinct t1.num as result from tp2 t1
inner join  tp2 t2 on t1.num = t2.num 
inner join  tp2 t3 on t2.num = t3.num 
where t1.id = t2.id-1 and t2.id = t3.id-1

-- 0.036 跟上一次出现比较，统计出现次数
SELECT DISTINCT a.Num ConsecutiveNums FROM (		# 主句
SELECT t.Num ,										# 子表1
       @cnt:=IF(@pre=t.Num, @cnt+1, 1) cnt,
       @pre:=t.Num pre
  FROM tp2 t, (SELECT @pre:=null, @cnt:=0) b) a		# 子表2,初始化两个变量
  WHERE a.cnt >= 3
	
	-- 出现次数倒序排，明显1出现最多次
	
	select @num := @num + 1 as rowno, num, count(1) c from tp2,(select @num := 0) a group by num order by c desc

注意

:=和=的区别

• =
  • 只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=
• :=
  • 不只在set和update时时赋值的作用，在select也是赋值的作用。

 

第二：实际项目中的用法

SELECT UserName,wcount,c_datasource_id,xDate
FROM
        (SELECT
            UserName,xDate,c_datasource_id,wcount,
        @num :=IF (@xDate = xDate and @datasource=c_datasource_id, @num + 1, 1) AS row_number,
                @xDate := xDate AS dummy,@datasource := c_datasource_id AS aa
        FROM
          (SELECT DATE_FORMAT(indatetime,'%Y-%m-%d %H:00:00') AS xDate,count(1) AS wcount,case when UserName='' then UserCode else UserName end as UserName,c_datasource_id
                        FROM ins_usage_myoperlog
                        WHERE indatetime BETWEEN DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d') and DATE_FORMAT(NOW(),'%Y-%m-%d')
                        and  c_datasource_id=:c_datasource_id
                        GROUP BY xDate,UserName,c_datasource_id
          ) bi_user_access
        ORDER BY xDate,wcount DESC
        ) AS x
WHERE x.row_number <= 5
ORDER BY xDate ASC,wcount DESC

-- 自定义变量的生命周期是在一个会话中有效
select @num := @num+1 as rowno,customer_id from bi_customer_product,(select @num :=0 ) r

官方文档关于自定义变量说明：https://dev.mysql.com/doc/refman/8.0/en/user-variables.html