mysql 固定多行转一列

图一

图二

本来想实现图一到图二的效果，通过下面这段代码可以实现

select substring_index(group_concat(dt1),',', 1) dt1,substring_index(substring_index(group_concat(dt1),',', 2),',', -1)dt2,substring_index(group_concat(dt1),',', -1)dt3 from (
select count(stationID)dt1 from dev_station UNION select count(detectorID)dt2 from dev_detector UNION select count(pdaID)dt3 from dev_pda
)a 

但是后台程序代码调用的时候出问题了，int类型实体字段获取的都是0，string类型实体字段都是乱码。

解决方法：

select CAST(substring_index(group_concat(dt1),',', 1) as SIGNED) dt1,CAST(substring_index(substring_index(group_concat(dt1),',', 2),',', -1) as SIGNED)dt2,CAST(substring_index(group_concat(dt1),',', -1) as SIGNED)dt3 from (
select count(stationID)dt1 from dev_station UNION select count(detectorID)dt2 from dev_detector UNION select count(pdaID)dt3 from dev_pda
)a 

经过索引截取的字符串需要转成整形。