表里面有这么一组数据:
code price
'MCU0-3' 15
'MCU0-3' 20
'MCU0-3' 22
'MCU3-15' 17
'MCU3-15' 16
'MCU3-15' -10
'MCU3-15' 50
'MCU3-27' 99
'MCU3-27' 96
'MCU3-27' 54
'MCU3-27' 14
'MCU3-27' 46
'MCU3-27' 86
现在需要得到这样的结果:
MCU0-3 MCU3-15 MCU3-27
15 17 99
20 16 96
22 -10 54
null 50 14
null null 46
null null 86
SQL语句是这样写的:
SELECT ID = IDENTITY(INT, 1, 1), price into #T1 from [table] where code = 'MCU0-3'
SELECT ID = IDENTITY(INT, 1, 1), price into #T2 from [table] where code = 'MCU3-15'
SELECT ID = IDENTITY(INT, 1, 1), price into #T3 from [table] where code = 'MCU3-27'
select t1.price as MCU0-3, t2.price as MCU3-15,t3.price as MCU3-27 from #T1 t1 FULL OUTER JOIN #T2 t2 on t1.id = t2.id FULL OUTER JOIN #T3 t3 on t2.id = t3.id
效率不高,但是这样写比较简单。用游标也能达到效果,但是效率怎么样我没有测试,我想应该不会比这个好多少。