有表如下
pos point startdate drop
204 2 2006-12-1 2
204 2 2006-12-2 2
204 2 2006-12-3 1
204 2 2006-12-4 2
204 2 2006-12-5 2
204 2 2006-12-6 2
204 2 2006-12-7 3
204 2 2006-12-8 0
204 2 2006-12-9 5
218 3 2006-12-1 1
218 3 2006-12-2 2
218 3 2006-12-3 2
218 3 2006-12-4 0
218 3 2006-12-5 1
218 3 2006-12-6 3
218 3 2006-12-7 5
218 3 2006-12-8 3
218 3 2006-12-9 4
数据是按照pos和point分类,每天各增加一条新的记录
我想得到这样的查询结果
pos point 2006-12-1 2006-12-2 2006-12-3 ......2006-12-9......
204 2 2 2 1 5
218 3 1 2 2 4
不知道怎么写才能实现呢?(看过一些关于行列转换的,看不太懂,请详细解释一下,先表示深深的谢意)
pos point startdate drop
204 2 2006-12-1 2
204 2 2006-12-2 2
204 2 2006-12-3 1
204 2 2006-12-4 2
204 2 2006-12-5 2
204 2 2006-12-6 2
204 2 2006-12-7 3
204 2 2006-12-8 0
204 2 2006-12-9 5
218 3 2006-12-1 1
218 3 2006-12-2 2
218 3 2006-12-3 2
218 3 2006-12-4 0
218 3 2006-12-5 1
218 3 2006-12-6 3
218 3 2006-12-7 5
218 3 2006-12-8 3
218 3 2006-12-9 4
数据是按照pos和point分类,每天各增加一条新的记录
我想得到这样的查询结果
pos point 2006-12-1 2006-12-2 2006-12-3 ......2006-12-9......
204 2 2 2 1 5
218 3 1 2 2 4
不知道怎么写才能实现呢?(看过一些关于行列转换的,看不太懂,请详细解释一下,先表示深深的谢意)
SET sql=''
SELECT sql=concat(concat(concat(concat(sql,',SUM(if (startdate = '''), startdate,''' , drop , 0 )) as [',startdate),']')) FROM (SELECT DISTINCT startdate FROM tmp) a
SELECT sql=concat(concat('SELECT pos, point ',sql),' FROM tmp GROUP BY point, point')
SET @s=sql;
PREPARE stmt FROM @s;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;