比如:在表a中有字段a1,a2,a3,里面有若干条记录,想得到这样的查询结果:
a1, a2 ,a3, a1, a2, a3,
大家说下好实现吗
比如:有记录
a1 a2 a3
1 2 3
4 5 6
7 8 9
10 11 12
现想得到这样的格式
a1 a2 a3 a1 a2 a3
1 2 3 4 5 6
7 8 9 10 11 12
a1, a2 ,a3, a1, a2, a3,
大家说下好实现吗
比如:有记录
a1 a2 a3
1 2 3
4 5 6
7 8 9
10 11 12
现想得到这样的格式
a1 a2 a3 a1 a2 a3
1 2 3 4 5 6
7 8 9 10 11 12
1 2 3 4 5 6
7 8 9 10 11 12关键是看有什么条件能让1 2 3 这条数据跟 4 5 6这条数据关联?为什么不是
1 2 3 10 11 12
7 8 9 4 5 6
id int identity(1,1) not null,
a1 int not null,
a2 int not null,
a3 int not null)
insert into pp(a1,a2,a3) values(1,2,3)
insert into pp(a1,a2,a3) values(4,5,6)
增加一个自动ID号就可以了
insert into pp(a1,a2,a3) values(7,8,9)
insert into pp(a1,a2,a3) values(10,11,12)select A.a1, A.a2, A.a3,B.a1,B.A2,B.a3 from
(select * from pp where (id+1)%2 = 0) A
left join
(select * from pp where id%2 = 0) B
on A.id + 1 = B.id
--测试删除
drop table pp
insert into @a(a1,a2,a3)
select 1,2,3
union all select 4,5,6
union all select 7,8,9
union all select 10,11,12select * from @aselect sum(case when id%2=1 then a1 else 0 end) as a1,
sum(case when id%2=1 then a2 else 0 end) as a2,
sum(case when id%2=1 then a3 else 0 end) as a3,
sum(case when id%2=0 then a1 else 0 end) as a1,
sum(case when id%2=0 then a2 else 0 end) as a2,
sum(case when id%2=0 then a3 else 0 end) as a3
from @a group by (id+1)/2必须要有ID!
insert T select 1, 2, 3
union all select 4, 5, 6
union all select 7, 8, 9
union all select 10, 11, 12select ID=identity(int, 1, 1), * into #T from Tselect tmp.a1, tmp.a2, tmp.a3,
a1=(select a1 from #T where ID=tmp.ID+1),
a2=(select a2 from #T where ID=tmp.ID+1),
a3=(select a3 from #T where ID=tmp.ID+1)
from #T tmp
where ID%2=1--result
a1 a2 a3 a1 a2 a3
----------- ----------- ----------- ----------- ----------- -----------
1 2 3 4 5 6
7 8 9 10 11 12(2 row(s) affected)