mysql-@用法

mysql的@用法

1，增加临时表，实现变量的自增

SELECT (@i:=@i+1),t.* FROM table_name t,(SELECT @i:=0) AS j

(@i:=@i+1)代表定义一个变量，每次叠加1；
(SELECT @i:=0) AS j 代表建立一个临时表，j是随便取的表名，但别名一定要的。

2，实现排序递增

SELECT (@i:=@i+1),p.*  FROM (
  SELECT * FROM t_admin ORDER BY create_time DESC 
)p,(SELECT@i:=0)k 

3，实现分组递增

SELECT
    @r:= case when @type=a.type then @r+1 else 1 end as rowNum,
    @type:=a.type as type,
    a.id
from 
    tmp_mytest a ,(select @r:=0 ,@type:='') b;

这里用了临时变量@type，因为对临时变量操作的时候，被使用的变量都是临时变量。

实战

INSERT INTO t_top (col_account,col_date,p_account,p_name)
SELECT b.col_account,b.col_date,b.p_account,b.p_name
FROM
(
SELECT
	@num :=
CASE
	
	WHEN @account = a.col_account THEN
	@num + 1 ELSE 1 
	END AS rownum,
	@account := a.col_account AS account,
	a.* 
FROM
	(SELECT * FROM zb_top  ORDER BY col_account,p_avg DESC) a,
( SELECT @num := 0, @account := '' ) j
) b where 6>b.rownum