有个表的数据比较的怪,要求
第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
...
第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
...
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
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-1) / 3) % 3=0
--测试
declare @t table (
field1 int,
field2 varchar(5)
)
insert @t select
1, 'a'
union all select
2, 'b'
union all select
3, 'c'
union all select
4, 'd'
union all select
5, 'e'
union all select
6, 'f'
union all select
7, 'g'
union all select
8, 'h'
union all select
9, 'i'
union all select
10, 'i'
union all select
11, 'i'
union all select
12, 'i'
union all select
13, 'i'
union all select
14, 'i'
union all select
15, 'i'
union all select
16, '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 @t a left join @t b
on (a.field1 % 3)=(b.field1 % 3)
and (a.field1 / 3)=(b.field1 / 3)-1
left join @t c
on (a.field1 % 3)=(c.field1 % 3)
and (a.field1 / 3)=(c.field1 / 3)-2
where ((a.field1-1) / 3) % 3=0--结果
f1 f2 f3 f4 f5 f6
----------- ----- ----------- ----- ----------- -----
1 a 4 d 7 g
2 b 5 e 8 h
3 c 6 f 9 i
10 i 13 i 16 i
11 i 14 i NULL NULL
12 i 15 i NULL NULL(所影响的行数为 6 行)
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-1) / 3) % 3=0
--测试
declare @t table (
field1 int,
field2 varchar(5)
)
insert @t select
1, 'a'
union all select
2, 'b'
union all select
3, 'c'
union all select
4, 'd'
union all select
5, 'e'
union all select
6, 'f'
union all select
7, 'g'
union all select
8, 'h'
union all select
9, 'i'
union all select
10, 'i'
union all select
11, 'i'
union all select
12, 'i'
union all select
13, 'i'
union all select
14, 'i'
union all select
15, 'i'
union all select
16, '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 @t a left join @t b
on (a.field1 % 3)=(b.field1 % 3)
and (a.field1 / 3)=(b.field1 / 3)-1
left join @t c
on (a.field1 % 3)=(c.field1 % 3)
and (a.field1 / 3)=(c.field1 / 3)-2
where ((a.field1-1) / 3) % 3=0--结果
f1 f2 f3 f4 f5 f6
----------- ----- ----------- ----- ----------- -----
1 a 4 d 7 g
2 b 5 e 8 h
3 c 6 f 9 i
10 i 13 i 16 i
11 i 14 i NULL NULL
12 i 15 i NULL NULL(所影响的行数为 6 行)