Select IsNull(A.ID,B.ID), IsNull(A.price,0), IsNull(A.price,0) from TEST1 A FULL JOIN TEST2 B On A.ID = B.ID
select aid=identity(int),* into #1 from test1 select aid=identity(int),* into #2 from test2 select id=isnull(a.id,b.id) ,[TEST1.Price]=isnull(a.Price,0) ,[TEST2.Price]=isnull(b.Price,0) from #1 a full join #2 b on a.id=b.id and(select count(*) from #1 where id=a.id and aid<=a.aid) =(select count(*) from #2 where id=b.id and aid<=b.aid) drop table #1,#2
有错误,改为 select *,identity(int,1,1) num into #tt1 from test1select *,identity(int,1,1) num into #tt2 from test2 select isnull(a.id,b.id) id, isnull(a.price,0) price1, isnull(b.price,0) price2 from #tt1 a full join #tt2 b on a.id = b.id and a.num = b.num
TO:zjcxc(邹建) 版主版主的方法我试了下,报列名ID无效..我再看了看方法.. and(select count(*) from #1 where id=a.id and aid<=a.aid) =(select count(*) from #2 where id=b.id and aid<=b.aid) 只有这两个地方出现ID.. ID = A.ID 和ID = B.ID 估计是这里的问题吧..不过我没理解版主的思路,也没弄明白这两句是什么意思..还请版主指教.
TO:wudan8057(一江春水向东流) from #tt1 a full join #tt2 b on a.id = b.id and a.num = b.num这句中,如果我的TEST1和TEST2表中ID同样的记录数不一样多,还是有问题..
TO:tdtjjiao(给分) 你的条件好像不够?我的?
你的原表不是id,price两个字段么? 下面两句中的id就是原表的idand(select count(*) from #1 where id=a.id and aid<=a.aid) =(select count(*) from #2 where id=b.id and aid<=b.aid)
--测试 create table TEST1(ID int,Price decimal(10,1)) insert TEST1 select 1,2 union all select 1,3 union all select 2,3.1 union all select 2,3.2 union all select 2,3.5create table TEST2(ID int,Price decimal(10,1)) insert TEST2 select 1,1.9 union all select 1,3.1 union all select 1,3.2 union all select 2,4 union all select 2,4.5 go--查询 select aid=identity(int),* into #1 from test1 select aid=identity(int),* into #2 from test2 select id=isnull(a.id,b.id) ,[TEST1.Price]=isnull(a.Price,0) ,[TEST2.Price]=isnull(b.Price,0) from #1 a full join #2 b on a.id=b.id and(select count(*) from #1 where id=a.id and aid<=a.aid) =(select count(*) from #2 where id=b.id and aid<=b.aid) order by id drop table #1,#2 go--删除测试 drop table test1,test2/*--结果 id TEST1.Price TEST2.Price ----------- ------------ ------------ 1 2.0 1.9 1 3.0 3.1 1 .0 3.2 2 3.1 4.0 2 3.2 4.5 2 3.5 .0(所影响的行数为 6 行) --*/
Select
IsNull(A.ID,B.ID),
IsNull(A.price,0),
IsNull(A.price,0)
from TEST1 A
FULL JOIN TEST2 B On A.ID = B.ID
select aid=identity(int),* into #1 from test1
select aid=identity(int),* into #2 from test2
select id=isnull(a.id,b.id)
,[TEST1.Price]=isnull(a.Price,0)
,[TEST2.Price]=isnull(b.Price,0)
from #1 a
full join #2 b on a.id=b.id
and(select count(*) from #1 where id=a.id and aid<=a.aid)
=(select count(*) from #2 where id=b.id and aid<=b.aid)
drop table #1,#2
1 2 1.9
1 3 1.9
1 2 3.1
1 3 3.1
1 2 3.2
1 3 3.2
2 3.1 4
2 3.2 4
2 3.5 4
2 3.1 4.5
2 3.2 4.5
2 3.5 4.5
这样的结果还不是我想要的..不管怎么样,也要谢谢两位..
select *,identity(int,1,1) num
into #tt1
from test1select *,identity(int,1,1) num
into #tt2
from test2
select
isnull(a.id,b.id) id,
isnull(a.price,0) price1,
isnull(b.price,0) price2
from #tt1 a full join #tt2 b on a.id = b.id and a.num = b.num
and(select count(*) from #1 where id=a.id and aid<=a.aid)
=(select count(*) from #2 where id=b.id and aid<=b.aid)
只有这两个地方出现ID..
ID = A.ID 和ID = B.ID
估计是这里的问题吧..不过我没理解版主的思路,也没弄明白这两句是什么意思..还请版主指教.
你的条件好像不够?我的?
下面两句中的id就是原表的idand(select count(*) from #1 where id=a.id and aid<=a.aid)
=(select count(*) from #2 where id=b.id and aid<=b.aid)
create table TEST1(ID int,Price decimal(10,1))
insert TEST1 select 1,2
union all select 1,3
union all select 2,3.1
union all select 2,3.2
union all select 2,3.5create table TEST2(ID int,Price decimal(10,1))
insert TEST2 select 1,1.9
union all select 1,3.1
union all select 1,3.2
union all select 2,4
union all select 2,4.5
go--查询
select aid=identity(int),* into #1 from test1
select aid=identity(int),* into #2 from test2
select id=isnull(a.id,b.id)
,[TEST1.Price]=isnull(a.Price,0)
,[TEST2.Price]=isnull(b.Price,0)
from #1 a
full join #2 b on a.id=b.id
and(select count(*) from #1 where id=a.id and aid<=a.aid)
=(select count(*) from #2 where id=b.id and aid<=b.aid)
order by id
drop table #1,#2
go--删除测试
drop table test1,test2/*--结果
id TEST1.Price TEST2.Price
----------- ------------ ------------
1 2.0 1.9
1 3.0 3.1
1 .0 3.2
2 3.1 4.0
2 3.2 4.5
2 3.5 .0(所影响的行数为 6 行)
--*/
改成了select [id]=isnull(a.id,b.id)才行..也不知道为什么..不过总之是行了..
谢谢版主及各位...