mysql行转列有关问题

mysql行转列问题
遇到个比较奇怪的问题
一个简单的行转列语句：
  SELECT max(CASE WHEN t2.para_id = 7 THEN t2.value ELSE 0 END) AS ‘7’,
  max(CASE WHEN t2.para_id = 6 THEN t2.value ELSE 0 END) AS ‘6’,
  max(CASE WHEN t2.para_id = 8 THEN t2.value ELSE 0 END) AS ‘8’,
  max(CASE WHEN t2.para_id = 9 THEN t2.value ELSE 0 END) AS ‘9’,
  max(CASE WHEN t2.para_id = 10 THEN t2.value ELSE 0 END) AS ’10’,
  max(CASE WHEN t2.para_id = 41 THEN t2.value ELSE 0 END) AS ’41’,
  max(CASE WHEN t2.para_id = 42 THEN t2.value ELSE 0 END) AS ’42’,
  max(CASE WHEN t2.para_id = 43 THEN t2.value ELSE 0 END) AS ’43’,
  max(CASE WHEN t2.para_id = 44 THEN t2.value ELSE 0 END) AS ’44’,
  max(CASE WHEN t2.para_id = 45 THEN t2.value ELSE 0 END) AS ’45’
  FROM parameter t1 LEFT JOIN para_state t2 ON t2.para_id = t1.para_id
 WHERE t1.para_id IN (7, 6, 8, 9, 10, 41, 42, 43, 44, 45)
  AND t2.gather_date = ‘2011-12-18 11:07:24.0’

直接执行sql的结果:
0 0.57 169.0 167.0 0.0 0 0 0 0 0
通过jdbc取出来的值全部未0.0，
0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
这是什么原因呢？

java取值代码为：
Connection connection = session.connection();
PreparedStatement pStatement = connection.prepareStatement(stringBuffer.toString());
pStatement.setDate(1, new java.sql.Date(gatherDate.getTime()));
logger.debug(gatherDate.toString());
logger.debug(stringBuffer.toString());
ResultSet reSet = pStatement.executeQuery();
while(reSet.next()){
objArray[0] = reSet.getDouble("7");
objArray[1] = reSet.getDouble("6");
System.out.println(objArray[1]);
objArray[2] = reSet.getDouble("8");
System.out.println(objArray[2]);
objArray[3] = reSet.getDouble("9");
System.out.println(objArray[3]);
objArray[4] = reSet.getDouble("10");
objArray[5] = reSet.getDouble("41");
objArray[6] = reSet.getDouble("42");
objArray[7] = reSet.getDouble("43");
objArray[8] = reSet.getDouble("44");
objArray[9] = reSet.getDouble("45");
}

——解决方案——————–
SELECT max(CASE WHEN t2.para_id = 7 THEN t2.value ELSE 0 END) AS C7,
 max(CASE WHEN t2.para_id = 6 THEN t2.value ELSE 0 END) AS C6,
 max(CASE WHEN t2.para_id = 8 THEN t2.value ELSE 0 END) AS C8,
 max(CASE WHEN t2.para_id = 9 THEN t2.value ELSE 0 END) AS C9,
 max(CASE WHEN t2.para_id = 10 THEN t2.value ELSE 0 END) AS C10,
 max(CASE WHEN t2.para_id = 41 THEN t2.value ELSE 0 END) AS C41,
 max(CASE WHEN t2.para_id = 42 THEN t2.value ELSE 0 END) AS C42,
 max(CASE WHEN t2.para_id = 43 THEN t2.value ELSE 0 END) AS C43,
 max(CASE WHEN t2.para_id = 44 THEN t2.value ELSE 0 END) AS C44,
 max(CASE WHEN t2.para_id = 45 THEN t2.value ELSE 0 END) AS C45
 FROM parameter t1 LEFT JOIN para_state t2 ON t2.para_id = t1.para_id
WHERE t1.para_id IN (7, 6, 8, 9, 10, 41, 42, 43, 44, 45)
 AND t2.gather_date = ‘2011-12-18 11:07:24.0’

换个字段名试试。
——解决方案——————–
value什么类型
max(CASE WHEN t2.para_id = 7 THEN t2.value ELSE 0 END) AS ‘7’,->
max(CASE WHEN t2.para_id = 7 THEN cast(t2.value as decimal(12,2)) ELSE 0 END) AS `7`

喜欢 (0)赏

[搞代码]

分享 (0)