处理这样数据的sql语句如何写?
有个表的数据比较的怪,要求
第1,4,7...行合并
第2,5,8...行合并
第3,6,9...行合并.
比如表1中
field1 field2
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
9 i
...
变成表2
f1 f2 f3 f4 f5 f6
1 a 4 d 7 g
2 b 5 e 8 h
3 c 6 f 9 i
...
[解决办法]
select a.field1 as f1,a.field2 as f2,
b.field1 as f3,b.field2 as f4,
c.field1 as f5,c.field2 as f6
from 表1 a left join 表1 b
on (a.field1 % 3)=(b.field1 % 3)
and (a.field1 / 3)=(b.field1 / 3)-1
left join 表1 c
on (a.field1 % 3)=(c.field1 % 3)
and (a.field1 / 3)=(c.field1 / 3)-2
where (a.field1 % 9)=0
