急问这样的查询结果用sql能写出吗?

[lyfxzzb]

比如:在表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
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

[hanker1314520]

create table pp(
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

[IT道路上的修行者]

declare @a table (id int identity(1,1),a1 int,a2 int ,a3 int)
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,12

select * from @a

select 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!

[stou]

到程序里控制吧。

[marco08]

create table T(a1 int, a2 int, a3 int)
insert T select 1, 2, 3
union all select 4, 5, 6
union all select 7, 8, 9
union all select 10, 11, 12

select ID=identity(int, 1, 1), * into #T from T

select 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)

[y_dong119]

不错的思考方法值得学习哈

[wojila]

不会吧,大哥,你没给我们你的条件我们怎么给你写呢?
不过看你的题目我觉得用子查询或者自表联结接可以了!更简单

[kourr2004]

学习，marco08(天道酬勤) 兄真是太厉害了，佩服！

[lyfxzzb]

是我没说明白,已经搞定,结贴,谢谢大家