暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / MySQL中group_concat中order by语句拿出来

MySQL中group_concat中order by语句拿出来

原创 姚崇 2023-10-03
790

原SQL如下：


select t.*,
       tb.summary,
       tb.busin_flag_name,
       tb.sub_acco_info,
       tb.capital_account,
       tb.capital_account_name
from
  (select ti.project_code,
          ti.project_code_alias,
          ttb.process_instance_id,
          ti.project_name,
          substring_index(group_concat(DISTINCT ttb.description ORDER BY ttb.serial_no asc separator ','), ',', 1) description,
          sum(ttb.amount) amount,
          case when locate(',', group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_name else ttb.bnk_acct_name end ORDER BY ttb.serial_no asc separator ',')) > 0 
           then substring_index(group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_name else ttb.bnk_acct_name end ORDER BY ttb.serial_no asc separator ','), ',', 1) || '等'
                           else group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_name else ttb.bnk_acct_name end ORDER BY ttb.serial_no asc separator ',') end racct_name,
          case when locate(',', group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_bnk_name else ttb.bnk_name end ORDER BY ttb.serial_no asc separator ',')) > 0 then substring_index(group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_bnk_name
                                  else ttb.bnk_name end ORDER BY ttb.serial_no asc separator ','), ',', 1) || '等'
              else group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_bnk_name else ttb.bnk_name end ORDER BY ttb.serial_no asc separator ',')
          end rbnk_name,
          case
              when locate(',', group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_no else ttb.bnk_acct_no end ORDER BY ttb.serial_no asc separator ',')) > 0 
              then substring_index(group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_no else ttb.bnk_acct_no end ORDER BY ttb.serial_no asc separator ','), ',', 1) || '等'
              else group_concat(DISTINCT case when ttb.trans_busi_type = '05000401' then ttb.stock_acct_no else ttb.bnk_acct_no end ORDER BY ttb.serial_no asc separator ',')
          end racct_no,
          ti.org_id,
          ti.fund_code,
     (select tpf.fund_name
      from tproject_fundinfo tpf
      where tpf.fund_code=ti.fund_code) as fund_name,
          ttb.sub_project_code,
          CONCAT(count(1)+'', '笔') det_count,
          date_format(ttb.pay_date, '%Y-%m-%d') pay_date,
          ttb.pmemo,
          ttb.broker_name,
          ttb.broker_no,
          ttb.branch_name,
          ttb.branch_no,
          '0500' trans_type,
                 substring_index(group_concat(DISTINCT case
                                                           when ttb.trans_busi_type = '05000401' then ttb.bnk_acct_name
                                                           else ttb.stock_acct_name
                                                       end
                                              ORDER BY ttb.serial_no asc separator ','), ',', 1) pacct_name,
                 substring_index(group_concat(DISTINCT case
                                                           when ttb.trans_busi_type = '05000401' then ttb.bnk_name
                                                           else ttb.stock_bnk_name
                                                       end
                                              ORDER BY ttb.serial_no asc separator ','), ',', 1) pbnk_name,
                 substring_index(group_concat(DISTINCT case
                                                           when ttb.trans_busi_type = '05000401' then ttb.bnk_acct_no
                                                           else ttb.stock_acct_no
                                                       end
                                              ORDER BY ttb.serial_no asc separator ','), ',', 1) pacct_no,
                 ttb.serial_no
   from tproject_info ti,
        tcapital_transfer_stock_busi ttb
   where ttb.process_instance_id in ('1a',
                                     '1a')
     and ttb.project_code = ti.project_code
   group by ttb.process_instance_id) t
LEFT JOIN tcapital_transfer_stock_busiexp tb on tb.serial_no = t.serial_no
 ;

简化一下

select t.*,
       tb.summary,
       tb.busin_flag_name,
       tb.sub_acco_info,
       tb.capital_account,
       tb.capital_account_name
from
  (select ti.project_code,
          ti.project_code_alias,
          ttb.process_instance_id,
          ti.project_name,
          substring_index(group_concat(DISTINCT ttb.description separator ','), ',', 1) description,
          sum(ttb.amount) amount,
          case when locate(',', group_concat(DISTINCT ttb.bnk_acct_name separator ',')) > 0 
          then substring_index(group_concat(DISTINCT ttb.bnk_acct_name separator ','), ',', 1) || '等'
              else group_concat(DISTINCT else ttb.bnk_acct_name separator ',')
          end racct_name,
          case
              when locate(',', group_concat(DISTINCT ttb.bnk_name separator ',')) > 0 
              then substring_index(group_concat(DISTINCT ttb.bnk_name separator ','), ',', 1) || '等'
              else group_concat(DISTINCT ttb.bnk_name separator ',')
          end rbnk_name,
          case
              when locate(',', group_concat(DISTINCT ttb.bnk_acct_no separator ',')) > 0 
              then substring_index(group_concat(DISTINCT ttb.bnk_acct_no separator ','), ',', 1) || '等'
              else group_concat(DISTINCT ttb.bnk_acct_no separator ',')
          end racct_no,
          ti.org_id,
          ti.fund_code,
          tpf.fund_name as fund_name,
          ttb.sub_project_code,
          CONCAT(count(1)+'', '笔') det_count,
          date_format(ttb.pay_date, '%Y-%m-%d') pay_date,
          ttb.pmemo,
          ttb.broker_name,
          ttb.broker_no,
          ttb.branch_name,
          ttb.branch_no,
          '0500' trans_type,
          substring_index(group_concat(DISTINCT ttb.stock_acct_name separator ','), ',', 1) pacct_name,
          substring_index(group_concat(DISTINCT ttb.stock_bnk_name separator ','), ',', 1) pbnk_name,
          substring_index(group_concat(DISTINCT ttb.stock_acct_no separator ','), ',', 1) pacct_no,
          ttb.serial_no
   from tproject_info ti inner join 
        (select * from tcapital_transfer_stock_busi order by serial_no asc) ttb on ttb.project_code = ti.project_code
        left join tproject_fundinfo tpf on tpf.fund_code=ti.fund_code
        where ttb.process_instance_id in ('1a', '1a')
   group by ttb.process_instance_id) t
LEFT JOIN tcapital_transfer_stock_busiexp tb on tb.serial_no = t.serial_no;

说明

group_concat中的order by是对分组后的数据培训的，从target list取出来是有条件的，最好order by的列一致
举例说明

drop table test;
create table test(id int,name text);

insert into test(id,name) values(1, 'aaa');
insert into test(id,name) values(1, 'aaa1');
insert into test(id,name) values(1, 'aaa2');
insert into test(id,name) values(1, '111');
insert into test(id,name) values(2, 'bbb');
insert into test(id,name) values(2, 'bbb');
insert into test(id,name) values(2, 'aaa');
insert into test(id,name) values(2, 'ccc');
insert into test(id,name) values(3, 'ccc');

select * from test;
select id,
group_concat(distinct name order by name desc ) as a 
from test group by id;
id|a                |
--+-----------------+
 1|aaa2,aaa1,aaa,111|
 2|ccc,bbb,aaa      |
 3|ccc              |

select id,
group_concat(distinct name order by name asc ) as a 
from test group by id;
id|a                |
--+-----------------+
 1|111,aaa,aaa1,aaa2|
 2|aaa,bbb,ccc      |
 3|ccc              |

说明：

select ttb.bnk_acct_name,
group_concat(DISTINCT ttb.bnk_acct_name) as gcn ,
group_concat(DISTINCT ttb.bnk_acct_name separator '｜'),
locate(',', group_concat(DISTINCT ttb.bnk_acct_name separator ',')),
case when locate(',', group_concat(DISTINCT ttb.bnk_acct_name separator ',')) > 0  -- 表示逗号偏移量大于0
          then substring_index(group_concat(DISTINCT ttb.bnk_acct_name separator ','), ',', 1) || '等'
              else group_concat(DISTINCT ttb.bnk_acct_name separator ',')
          end racct_name 
   from  tcapital_transfer_stock_busi ttb  
   group by ttb.process_instance_id order by 4 desc;
  • group_concat (distinct xxx)分组去重
  • 用separator表示分隔，默认是逗号分隔，
  • LOCATE()函数用于查找指定字符在另一个字符串中的位置，并返回该位置。
    该函数也可用于查找字符串中是否包含子字符串。该函数采用三个参数，子字符串、大字符串以及可选的位置偏移。
select locate(',','abc,def,') from dual;
返回4
墨力计划
最后修改时间：2023-10-03 17:49:12
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

SQL优化案例（共7篇）
各种数据库下SQL优化案例

评论