Mysql按指定分组排序

 

 

-- Mysql按指定分组排序
select 
     t5.task_id
    ,concat('TH-',t5.proj_name,'-',lpad(rn,7,'0')) as id
    ,t5.complaint               -- 详细地址
    ,t5.requester               -- 联络人
    ,t5.phone                   -- 电话
    ,t5.urgency                 -- 报修情况
    ,t5.accept_time             -- 开单时间
    ,t5.accept_operator_name    -- 维修人员
    ,concat(t5.position_name,t5.class_full_name,t5.problem_name,t5.`subject`) as context
from (
    SELECT
        @r:= CASE WHEN @rank=te.proj_name THEN @r+1 ELSE 1 END AS rn,
        @rank:=te.proj_name AS TYPE,
        te.* 
    FROM (
        SELECT 
             t1.code
            ,t4.name as proj_name
            ,concat('TH-',t4.name,'-',substr(t1.code,length(t1.code)-3)) as id
            ,t1.complaint               -- 详细地址
            ,t1.requester_member_id     -- 
            ,t1.requester               -- 联络人
            ,t1.phone                   -- 电话
            ,t1.urgency                 -- 报修情况
            ,t1.accept_time             -- 开单时间
            ,t1.accept_operator         -- 
            ,t1.accept_operator_name    -- 维修人员
            ,t2.class_id                -- 
            ,t3.position_id             -- 
            ,case when ifnull(t3.position_name,'') <> '' then concat(t3.position_name,';') else '' end     as position_name   -- 报修内容_问题部位
            ,case when ifnull(t2.class_full_name,'') <> '' then concat(t2.class_full_name,';') else '' end as class_full_name -- 报修内容_问题分类1
            ,case when ifnull(t2.problem_name,'') <> '' then concat(t2.problem_name,';') else '' end       as problem_name    -- 报修内容_问题分类2
            ,t1.`subject`                                                                                  as subject         -- 报修内容_任务内容及补充说明
            ,concat(ifnull(t3.position_name,''),'；',ifnull(t2.class_full_name,''),'；',ifnull(t2.problem_name,''),'；',ifnull(t1.`subject`,'')) as context
            ,t1.id as task_id
        FROM k_task t1
        left join k_task_problem t2
            on  t2.task_id = t1.id and t2.is_deleted = 0
        left join k_task_problem_position t3
            on t3.task_id = t1.id and t3.is_deleted = 0
        left join t_project t4
            on t4.id = t1.proj_id and t4.is_deleted = 0
        where t1.is_deleted = 0
    ) te
    ,(SELECT @r:=0 ,@rank:='') b
    ORDER BY te.code
) t5
where 1 = 1
    ${IF(taskId='', " and (1=2) ", CONCATENATE(" and task_id in ('", REPLACE(taskId, ',', "','"), "')"))}
;

 

-- 数据添加序号

select
      id
     ,name
     ,(@i:=@i+1) as rn
from (
    SELECT 11 as id,'a' as name union all
    SELECT 12 as id,'b' as name union all
    SELECT 13 as id,'c' as name union all
    SELECT 14 as id,'d' as name
) t1
,(select @i:=0) t2
;