1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| SELECT GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(pa.env = ''', env, ''', pa.progress, -1)) AS ', "'",e.env_name,"'" ) ) INTO @sql FROM t3,(select @sql:= NULL) tmp, t_env e where env = e.env_id;
root@localhost 17:44:40 [fanboshi]> select @sql; + | @sql | + | MAX(IF(pa.env = '1', pa.progress, NULL)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, NULL)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, NULL)) AS 'Prod' | +
select @sql:=CONCAT('SELECT pa.title , ', @sql, ' FROM t3 pa GROUP BY pa.title'); + | @sql:=CONCAT('SELECT pa.title , ', @sql, ' FROM t3 pa GROUP BY pa.title') | + | SELECT pa.title , MAX(IF(pa.env = '1', pa.progress, -1)) AS 'RC',MAX(IF(pa.env = '2', pa.progress, -1)) AS 'Stage',MAX(IF(pa.env = '3', pa.progress, -1)) AS 'Prod' FROM t3 pa GROUP BY pa.title | + 1 row in set (0.00 sec) 其实这里已经把拼接好的sql查出来了, 程序直接拿着个结果去执行应该也是可以的, 可能不用后面在使用prepare statement了
PREPARE stmt FROM @sql; EXECUTE stmt; + | title | RC | Stage | Prod | + | 工单1 | 完成 | 完成 | 待审核 | | 工单2 | 待审核 | -1 | -1 | + DEALLOCATE PREPARE stmt;
|