这样的SQL该怎么写？
create table test1
(
id1 varchar(10),
name1 varchar(20)
)
insert into test1 select '01','a1'
insert into test1 select '02','a2'
create table test2
(
id2 varchar(10),
name2 varchar(20)
)
insert into test2 select '03','b1'
insert into test2 select '04','b2'
/*
要得到这样的报表：
id1 name1 id2 name2
01 a1 03 b1
02 a2 04 b2
*/
drop table test1
drop table test2
[解决办法]
- SQL code
create table test1 ( id1 varchar(10), name1 varchar(20) ) insert into test1 select '01','a1' insert into test1 select '02','a2' create table test2 ( id2 varchar(10), name2 varchar(20) ) insert into test2 select '03','b1' insert into test2 select '04','b2' select *from test1 a left join test2 b on a.id1+2 = b.id2drop table test1 drop table test2 /*id1 name1 id2 name2 ---------- -------------------- ---------- -------------------- 01 a1 03 b102 a2 04 b2（所影响的行数为 2 行）
[解决办法]
- SQL code
create table test1 ( id1 varchar(10), name1 varchar(20) ) insert into test1 select '01','a1' insert into test1 select '02','a2' create table test2 ( id2 varchar(10), name2 varchar(20) ) insert into test2 select '03','b1' insert into test2 select '04','b2' select m.id1 , m.name1,n.id2 , n.name2 from(select * , px = (select count(1) from test1 where id1 < t.id1) + 1 from test1 t) mfull join(select * , px = (select count(1) from test2 where id2 < t.id2) + 1 from test2 t) non m.px = n.px/* id1 name1 id2 name2 ---------- -------------------- ---------- -------------------- 01 a1 03 b102 a2 04 b2（所影响的行数为 2 行）*/ drop table test1 drop table test2
[解决办法]
- SQL code
create table test1 ( id1 varchar(10), name1 varchar(20) ) insert into test1 select '01','a1' insert into test1 select '02','a2' create table test2 ( id2 varchar(10), name2 varchar(20) ) insert into test2 select '03','b1' insert into test2 select '04','b2' --2000select identity(int,1,1) as id,* into #1 from test1select identity(int,1,1) as id,* into #2 from test2select id1,name1,id2,name2 from #1 a left join #2 b on a.id=B.id--2005 select id1,name1,id2,name2from (select ROW_NUMBER()over(order by getdate()) as rn ,* from test1) aleft join (select ROW_NUMBER()over(order by getdate()) as rn ,* from test2) bon a.rn=b.rn
[解决办法]
生成序列pid
然后(pid-1)/2分组
[解决办法]
如果是2005,改用row_number()
- SQL code
select m.id1 , m.name1,n.id2 , n.name2 from(select * , px = row_number() over(order by id1) from test1 t) mfull join(select * , px = row_number() over(order by id2) from test2 t) non m.px = n.px
[解决办法]
[解决办法]
- SQL code
create table test1 ( id1 varchar(10), name1 varchar(20) ) insert into test1 select '01','a1' insert into test1 select '02','a2' create table test2 ( id2 varchar(10), name2 varchar(20) ) insert into test2 select '03','b1' insert into test2 select '04','b2' select id1,name1,id2,name2from (select row_number()over(order by getdate()) as id ,* from test1) aleft join (select row_number()over(order by getdate()) as id ,* from test2) bon a.id=b.id/*id1 name1 id2 name2---------- -------------------- ---------- --------------------01 a1 03 b102 a2 04 b2(2 行受影响)*/
[解决办法]
- SQL code
create table test1 ( id1 varchar(10), name1 varchar(20) ) insert into test1 select '01','a1' insert into test1 select '02','a2' create table test2 ( id2 varchar(10), name2 varchar(20) ) insert into test2 select '03','b1' insert into test2 select '04','b2' SELECT IDD=IDENTITY(INT,1,1),* INTO #TA FROM TEST1 SELECT IDD=IDENTITY(INT,1,1),* INTO #TB FROM TEST2SELECT ID1,NAME1,ID2,NAME2 FROM #TA A,#TB B WHERE A.IDD=B.IDD（所影响的行数为 2 行）ID1 NAME1 ID2 NAME2 ---------- -------------------- ---------- -------------------- 01 a1 03 b102 a2 04 b2（所影响的行数为 2 行）
[解决办法]
先说个事情,楼主的发帖值得表扬.省去我很多时间.
建议版主把这个发贴做为标样,给那些不会提问题的人做个榜样.
[解决办法]
用row_number ... over生成序号列时，最好用full join来做链接，这样可以避免两边的记录数目不相等，可能会丢失一些数据