Mysql sql查询统计语句

-- 查询 统计同一张表中 各种情况， 列转行
SELECT
    a.a1 '是否有记录'
    , b.b1 '时间是否匹配'
    , c.c1 '中间户是否匹配'
    , d.d1 '金额是否匹配'
FROM
    ( SELECT count( * )a1 FROM repay_diff WHERE assets_id = 33241 AND financier_id = 462107 ) a
    LEFT JOIN ( SELECT count( * ) b1 FROM repay_diff WHERE assets_id = 33241 AND financier_id = 462107 AND repay_date = '2018-05-31' ) b ON 1 = 1
    LEFT JOIN ( SELECT count( * ) c1  FROM repay_diff WHERE assets_id = 33241 AND financier_id = 462107 AND repay_date = '2018-05-31' AND charge_id = 453159 ) c ON 1 = 1
    LEFT JOIN ( SELECT count( * ) d1 FROM repay_diff WHERE assets_id = 33241 AND financier_id = 462107 AND repay_date = '2018-05-31' AND amt = 228.8733 AND charge_id = 453159 ) d ON 1 =1

-- 根据类型不同 统计汇总金额
        SELECT
        sum( CASE type WHEN 1 THEN amt ELSE 0 END ) sum_one,
        sum( CASE type WHEN 2 THEN amt ELSE 0 END ) sum_two
        FROM
        table_a

group_concat函数配合distinct使用

select group_concat(distincat name) from tableA group by time

group_concat 在mysql中默认长度1024 超过会自动截取

  show variables like 'group_concat_max_len';

使用命令

    SET GLOBAL group_concat_max_len=102400;
    SET SESSION group_concat_max_len=102400;

大坑