我有这样的数据:
id_jarak idkota1 idkota2 jarak
1 1 2 1
2 1 3 2
3 1 4 3
4 2 1 1
5 2 3 5
8 3 5 8
7 3 2 5
6 3 1 2
9 4 1 3
10 4 5 9
11 5 3 8
12 5 4 9
我尝试从该数据中获取数据透视和矩阵作为我的查询
SELECT a.idkota1,
( IF(a.idkota2 = '1' or b.idkota1 = '1', a.jarak, 0 ) ) AS 1s,
( IF(a.idkota2 = '2' or b.idkota1 = '2', a.jarak, 0 ) ) AS 2s,
( IF(a.idkota2 = '3' or b.idkota1 = '3', a.jarak, 0 ) ) AS 3s,
( IF(a.idkota2 = '4' or b.idkota1 = '4', a.jarak, 0 ) ) AS 4s,
( IF(a.idkota2 = '5' or b.idkota1 = '5', a.jarak, 0 ) ) AS 5s
FROM ms_jarak_kota as a
inner join ms_jarak_kota as b
on a.idkota1>=b.idkota2
GROUP BY b.idkota2
但是结果显示像这样
idkota1 1s 2s 3s 4s 5s
1 0 1 0 0 0
2 1 0 0 0 0
3 2 0 0 0 0
4 3 0 0 0 0
5 0 0 8 0 0
我的期望结果是这样的
1s 2s 3s 4s 5s
1 0 1 2 3
2 1 0 5
3 2 5 0 8
4 3 0 9
5 8 9 0
我的查询有问题,如何解决?谢谢
尝试这种方式:
SELECT idkota1,
MAX(CASE WHEN idkota2 = 1 THEN jarak END) AS '1s',
MAX(CASE WHEN idkota2 = 2 THEN jarak END) AS '2s',
MAX(CASE WHEN idkota2 = 3 THEN jarak END) AS '3s',
MAX(CASE WHEN idkota2 = 4 THEN jarak END) AS '4s',
MAX(CASE WHEN idkota2 = 5 THEN jarak END) AS '5s'
FROM ms_jarak_kota
GROUP BY idkota1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句