sql 踩坑之联表查询

连表查询

所需结果，用户下的角色信息

 <select id="ticketNameList" resultType="com.lekai.anybussfun.dto.RefundRespDto">
        select
        de.id,
        GROUP_CONCAT(
        CONCAT(
        m.row_num,
        '排',
        m.seat_number,
        '号'
        ) SEPARATOR ','
        ) AS seatName,
        sd.ticket_level_name  as ticketLevelName,
        de.price,
        record.refund_money as refundMoney,
        record.refund_percent as refundPercent
        from any_fun_ticket_refund_record record
        left join any_fun_ticket_pocket_detail de
        on record.buy_ticket_id=de.id
        left join any_fun_sessions_tickit_detail sd
        on de.ticket_id=sd.id
        LEFT JOIN any_fun_ticket_pocket_seat s on de.id = s.buy_ticket_id and s.del_flag = 0
        LEFT JOIN any_fun_seat_map m ON s.seat_id = m.id AND m.del_flag = 0
        where record.refund_id=#{refundId}
    </select>

1、联表中，各从表的判断应该在on后执行。主表的判断应该在where中

2、字段的拼接

        GROUP_CONCAT(
        CONCAT(
        m.row_num,
        '排',
        m.seat_number,
        '号'
        ) SEPARATOR ','
        ) AS seatName,