mysql创建临时表、树状菜单查找

快速创建

-- 快速创建表aaa
create temporary table aaa(id int) ;
create temporary table aaa(id int, index(id)) engine=innodb;

-- 向临时表插入数据
insert into aaa  select a.id
from answer1 a,
     answer1 b
where a.subject_id = b.subject_id
  and a.subject_no = b.subject_no
  and a.option_name = b.option_name
  and a.group_name = b.group_name
  and a.user_answer = b.user_answer
  and a.user_id = b.user_id
  and a.id > b.id;

 

sql中临时定义变量

select e.id, name, salary, dept, (@num :=@num +1)  from employee e,
        ( select  @num :=0) b
where 1=1
order by salary

 

根据部门查找工资前3的员工

-- 定义临时变量 @num 用于计算，@dept 用于标识当前部门， 如果当前这一记录是与@dept相等，则 @num+1, 否则 @num =0 ,并且@dept :=当前部门
select * from (
select e.id, name, salary, dept,
       if(@dept = dept = 0,concat ( (@num:=1),  (@dept := dept)),@num := @num + 1 ) dosomething,
           @num num
from employee e,
     ( select @num :=0,@dept := 'xxxxx' ) b
where 1=1
order by dept,salary desc ) a where a.num < 4;

-- 简化 
select e.*,
       @num := case when @dept=e.dept then @num+1 else 1 end num,
       @dept := e.dept as the_dept
from employee e,
     ( select @num :=0,@dept := '' ) b
where 1=1
order by dept,salary desc ;


-- 再简化
select e.*,
       @num := if( @dept=e.dept , @num+1 , 1  )num,
       @dept := e.dept as the_dept
from employee e,
     ( select @num :=0,@dept := '' ) b
where 1=1
order by dept,salary desc ;

 

树状菜单查找，找所有子节点

-- 根据 pid 找出其所有子节点及子子节点
select m.* from (
                  select @menu mmm,
                         (select @menu := group_concat(m.menu_id) from sys_menu m where find_in_set(m.pid,@menu)) mgc
                  from sys_menu s,
                       (select @menu := 1) t1
                  where @menu is not null
              ) t2 , sys_menu m
where find_in_set(m.menu_id,mmm)

找所有父节点

-- 根据id 找出他的父id 祖父id
select *
from (
         select @menu c, (select @menu := group_concat(t0.pid) from sys_menu t0 where t0.menu_id= @menu) a
         from sys_menu t1,
              (select @menu := 3) t2
         where @menu is not null
     ) b,sys_menu m
where m.menu_id = b.c
;

 

每隔3条取一条数据

select * from(
        select (select @num := @num + 1) nums, m.*
        from sys_menu m,
             (select @num := 0 ) t1 ) bbb
where bbb.nums %3 =0