mysql行转列不确定列数
sql:
1 set group_concat_max_len=100000; 2 SET @sql = NULL; 3 SELECT 4 GROUP_CONCAT(DISTINCT 5 CONCAT( 6 'SUM(IF(DATE_FORMAT(p.PAYMENT_DATE, ''','%Y-%m',''') = ''', 7 DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m'), 8 ''', p.TOTAL_FEE, 0)) AS ''', 9 DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m'), '''' 10 ) 11 ) INTO @sql 12 From bs_bill p 13 Where DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m-%d') >= '2022-01-01' 14 and DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m-%d') <'2023-01-01' ; 15 16 17 SET @sql = CONCAT('Select p.CONTRACT_ID,d.LESSEE, ', @sql, 18 ' From bs_bill p INNER JOIN bs_contract d on p.CONTRACT_ID = d.CONTRACT_ID 19 where DATE_FORMAT(p.PAYMENT_DATE, ''%Y-%m-%d'') >= ''2022-01-01'' 20 and DATE_FORMAT(p.PAYMENT_DATE, ''%Y-%m-%d'') <= ''2023-01-01'' 21 GROUP BY p.CONTRACT_ID,d.LESSEE'); 22 23 PREPARE stmt FROM @sql; 24 EXECUTE stmt; 25 DEALLOCATE PREPARE stmt;
结果：
