只能实现到这一步:
a1 b1 a2 b2 a3 b3
1 1 0 0 0 0
2 2 0 0 0 0
0 0 1 1 0 0
0 0 2 2 0 0
0 0 0 0 1 1
0 0 0 0 2 2
如何合成;
a1 b1 a2 b2 a3 b3
1 1 1 1 1 1
2 2 2 2 2 2谢谢。
a1 b1 a2 b2 a3 b3
1 1 0 0 0 0
2 2 0 0 0 0
0 0 1 1 0 0
0 0 2 2 0 0
0 0 0 0 1 1
0 0 0 0 2 2
如何合成;
a1 b1 a2 b2 a3 b3
1 1 1 1 1 1
2 2 2 2 2 2谢谢。
(select min(a1) from tb where a1 <> 0),
(select min(b1) from tb where b1 <> 0),
(select min(a2) from tb where a2 <> 0),
(select min(b2) from tb where b2 <> 0),
(select min(a3) from tb where c3 <> 0),
(select min(b3) from tb where b3 <> 0)
union
select
(select max(a1) from tb where a1 <> 0),
(select max(b1) from tb where b1 <> 0),
(select max(a2) from tb where a2 <> 0),
(select max(b2) from tb where b2 <> 0),
(select max(a3) from tb where c3 <> 0),
(select max(b3) from tb where b3 <> 0)
insert into tb values(1 , 1, 0 , 0 , 0 , 0 )
insert into tb values(2 , 2, 0 , 0 , 0 , 0 )
insert into tb values(0 , 0, 1 , 1 , 0 , 0 )
insert into tb values(0 , 0, 2 , 2 , 0 , 0 )
insert into tb values(0 , 0, 0 , 0 , 1 , 1 )
insert into tb values(0 , 0, 0 , 0 , 2 , 2 )
goselect
(select min(a1) from tb where a1 <> 0),
(select min(b1) from tb where b1 <> 0),
(select min(a2) from tb where a2 <> 0),
(select min(b2) from tb where b2 <> 0),
(select min(a3) from tb where a3 <> 0),
(select min(b3) from tb where b3 <> 0)
union
select
(select max(a1) from tb where a1 <> 0),
(select max(b1) from tb where b1 <> 0),
(select max(a2) from tb where a2 <> 0),
(select max(b2) from tb where b2 <> 0),
(select max(a3) from tb where a3 <> 0),
(select max(b3) from tb where b3 <> 0)
drop table tb/*
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
2 2 2 2 2 2(所影响的行数为 2 行)
*/
select id=identity(int,1,1), A=round(rand()*10,0),B=round(rand()*10,0) into #t
go
insert into #t select A=round(rand()*10,0),B=round(rand()*10,0)
go
select * from #t order by id asc1 6 0
2 3 5
3 8 9
4 5 2
5 1 2
6 5 5
7 7 5
8 9 0
9 9 2
10 1 7
11 6 0
12 1 3
13 5 5
14 3 1select * from (
select A1=A,B1=B,A2=0,B2=0,A3=0,B3=0 from #t where id>=1 and id<=5
union all
select A1=0,B1=0,A2=A,B2=B,A3=0,B3=0 from #t where id>=6 and id<=10
union all
select A1=0,B1=0,A2=0,B2=0,A3=A,B3=B from #t where id>=11 and id<=15
) t
A1 B1 A2 B2 A3 B3
3 5 0 0 0 0
8 9 0 0 0 0
5 2 0 0 0 0
1 2 0 0 0 0
6 0 0 0 0 0
0 0 5 5 0 0
0 0 7 5 0 0
0 0 9 0 0 0
0 0 9 2 0 0
0 0 1 7 0 0
0 0 0 0 6 0
0 0 0 0 1 3
0 0 0 0 5 5
0 0 0 0 3 1
select rowid = row_number() over (order by getdate()),A1=A,B1=B,A2=0,B2=0,A3=0,B3=0 from #t where id>=1 and id <=5
)a join
(select rowid = row_number() over (order by getdate()), A1=0,B1=0,A2=A,B2=B,A3=0,B3=0 from #t where id>=6 and id <=10
) b join
(select rowid = row_number() over (order by getdate()),A1=0,B1=0,A2=0,B2=0,A3=A,B3=B from #t where id>=11 and id <=15
) c
where a.rowid = b.rowid and a.rowid = c.rowid
if object_id('t')is not null drop table t
go
create table t(a1 int,b1 int, a2 int,b2 int,a3 int,b3 int)
insert t select
1, 1 ,0, 0 ,0, 0 union all select
2 , 2 ,0 , 0 ,0 , 0 union all select
0 ,0 ,1 ,1 ,0 ,0 union all select
0 ,0 ,2 ,2, 0 ,0 union all select
0 ,0 ,0 ,0 , 1 ,1 union all select
0 ,0 ,0 ,0, 2 ,2 --select name,a1 b1 a2 b2 a3 b3
select id=identity(int,1,1),* into #t from tselect t1.a1,t1.b1,t2.a2,t2.b2,t3.a3,t3.b3 from #t t1,#t t2,#t t3
where t1.id=t2.id-2 and t1.id=t3.id-4a1 b1 a2 b2 a3 b3
----------- ----------- ----------- ----------- ----------- -----------
1 1 1 1 1 1
2 2 2 2 2 2(2 行受影响)
drop table #t
if object_id('t')is not null drop table t
go
create table t(a1 int,b1 int, a2 int,b2 int,a3 int,b3 int)
insert t select
3, 5 ,0, 0, 0, 0 union all select
8, 9 ,0, 0, 0, 0 union all select
5, 2 ,0, 0, 0, 0union all select
1, 2 ,0, 0, 0, 0 union all select
6, 0, 0, 0, 0, 0 union all select
0, 0, 5, 5, 0, 0union all select
0, 0, 7, 5, 0, 0union all select
0, 0, 9, 0, 0, 0 union all select
0, 0, 9, 2, 0, 0 union all select
0, 0, 1, 7, 0, 0 union all select
0, 0, 0 ,0, 6, 0 union all select
0, 0, 0 ,0, 1, 3 union all select
0, 0, 0 ,0, 5, 5 union all select
0, 0, 0, 0, 3, 1select id=identity(int,1,1),* into #t from tselect t1.a1,t1.b1,t2.a2,t2.b2,t3.a3,t3.b3 from #t t1
left join #t t2 on t1.id=t2.id-5
left join #t t3 on t1.id=t3.id-10
where t1.id<=5
a1 b1 a2 b2 a3 b3
----------- ----------- ----------- ----------- ----------- -----------
3 5 5 5 6 0
8 9 7 5 1 3
5 2 9 0 5 5
1 2 9 2 3 1
6 0 1 7 NULL NULL(5 行受影响)
drop table #t