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;

大きな穴