关于多表联查，多行合并一行

有三张表

MySQL的写法：group_concat

第一种写法：

select u.name,group_concat(r.name separator ',') rolesname
from user u left join user_role ur on u.id = ur.user_id
left join role r on ur.role_id = r.id
GROUP BY u.name

 第二种写法:

SELECT u.name,group_concat(r.name separator ',') rolesname
FROM user u , user_role ur, role r
WHERE u.id = ur.user_id
and r.id = ur.role_id
group by u.name;

 

Oracle的写法：wm_concat

SELECT u.name,to_char(wm_concat(r.name)) rolesname
FROM user u , user_role ur, role r
WHERE u.id = ur.user_id
and r.id = ur.role_id
group by u.name;

当其中一个为主表，其他以主表ID相关联，如

SELECT * FROM
       (SELECT A.*, ROWNUM RN  FROM
               (select om.TKT_USERID,om.ORDER_STATUS,om.PNR_NO,om.RES_OFFICEID,
               om.PNR_STATUS,om.PAY_TYPE, op.TKT_FACEFARE,op.TKT_DATE as TKT_DATE,
               op.Agent_TotalFare,op.Agent_rate,op.NetFare,op.TaxesBreak,op.Farebasis,
               se.ROUTING,se.ORG_TIME,se.BOOKINGCLASS,se.AIRLINE,
                op.PASS_NAME,op.Tkt_No,op.Tour_code
                FROM ORDER_MAIN om   
                     left join ORDER_PSG op on om.rec_no=op.rec_no  
                     LEFT JOIN (SELECT os.rec_no, wmsys.wm_concat (os.org||os.dest) as ROUTING,
                          wmsys.wm_concat(to_char(os.ORG_TIME,'yyyy/MM/dd'))as ORG_TIME,wm_concat (os.CLASS) AS BOOKINGCLASS,
                           wmsys.wm_concat(os.AIRLINE) as AIRLINE    
                           FROM order_segment os
                           where os.seg_status='OK'            
                           GROUP BY os.rec_no
                           )se ON om.rec_no=se.rec_no   
                        where  om.res_userid='DL395409'
                        and to_date( om.RES_DATE,'yyyy-mm-dd hh24:mi:ss')
                        between to_date('2017-12-13 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2017-12-29 23:59:59','yyyy-mm-dd hh24:mi:ss')
                        ORDER BY to_date(OM.RES_DATE,'yyyy-mm-dd hh24:mi:ss'), OM.ORDER_STATUS
                 ) A  WHERE ROWNUM <= '6'  
      ) WHERE RN >= '1'

转载于:https://www.cnblogs.com/ming92/p/8243249.html