mysql中一个字段根据另一字段的值分割为不同列

1.数据结构如下:

  vehicleId    mileage_type    mileage

  11AM6897             0        120

  11AM6897             1        60

  13AY9180      0        100

  31D07216      1        80

 

2.想得到一下结果:

  vehicleId    excutionMileage    nonExcutionMileage

  11AM6897             120            60

  13AY9180      100            0

  31D07216      0             80

 

3.sql:

SELECT
    vehicle_id,IF (mileage_type = 1, mileage, 0) AS nonExcutionMileage,IF (mileage_type = 0, mileage, 0) AS excutionMileage
FROM
    gps_basis.having_opened_vehicle_mileage_detail
SELECT vehicle_id,date,
 case mileage_type when 0 then mileage else 0 end excutionMileage, 
 case mileage_type when 1 then mileage else 0 end nonExcutionMileage
FROM gps_basis.having_opened_vehicle_mileage_detail 

 

posted @ 2017-05-19 14:45  龍清扬  阅读(4130)  评论(0编辑  收藏  举报