【MySQL】 MySQL常用查询集锦

-- 查询语句结果显示行号
select @r:=@r+1 as rowno , a.* from  my_tb a ,(select @r:=0) b

 

 

-- 遍历节点下的所有子节点

select dept_no,ischild from (
              select t1.dept_no,
              if(find_in_set(parent_dept_no, @pids) > 0, @pids := concat(@pids, ',', dept_no), 0) as ischild
              from (
                   select dept_no,parent_dept_no from t_dept t  order by parent_dept_no, dept_no
                  ) t1,
                  (select @pids := '00110000') t2
             ) t3 where ischild != 0;

 

-- SQL中加入条件查询的方式
SELECT  SUM(IFNULL(t1.data_value,0)) value,
    CASE t1.peak_valley_flag WHEN 1 THEN '峰' WHEN 2 THEN '平' WHEN 3 THEN '谷' WHEN 4 THEN '尖'END AS name
    FROM t_hourly_statistic t1 
    left join t