有两个表:
table A ID VALUE
1 a
1 b
2 c
table B ID VALUE
1 A
2 B
2 C
根据两个表的id进行合并,需要将它们合并成这种形式:
ID VALUE1 VALUE2
1 a A
1 b NULL
2 c B
2 NULL c
table A ID VALUE
1 a
1 b
2 c
table B ID VALUE
1 A
2 B
2 C
根据两个表的id进行合并,需要将它们合并成这种形式:
ID VALUE1 VALUE2
1 a A
1 b NULL
2 c B
2 NULL c
谢谢,要求用最少的SQL语句完成。
insert @A
select 1, 'a' union all
select 1, 'b' union all
select 2, 'c'
declare @B table (ID int, Value varchar(10))
insert @B
select 1, 'A' union all
select 2, 'B' union all
select 2, 'C'
select ID = case when a.ID is not null then a.ID else b.ID end, Value1 = a.Value, Value2 = b.Value from @A a full join @B b on a.id = b.id and a.Value = b.Value order by ID
1 b NULL
2 NULL B
2 c C昨夜小楼的运行结果是错误的,我希望的结果第一个id 2 的值是 2,c,B。
应该如何改语句?
在线等结果!成功就揭帖!!
谢谢大家
create table tabA(ID int, Value varchar(10))
insert tabA
select 1, 'a' union all
select 1, 'b' union all
select 2, 'c'
create table tabB(ID int, Value varchar(10))
insert tabB
select 1, 'A' union all
select 2, 'B' union all
select 2, 'C'select id=isnull(a.id,b.id),
c1=case when (select 1 from (select id,cc=min(value) from tabb group by id) t where t.id=id and t.cc=b.value)=1 then a.value else null end,
c2=case when (select 1 from (select id,cc=min(value) from taba group by id) t where t.id=id and t.cc=a.value)=1 then b.value else null end
from tabA a full join tabB b on a.id = b.id order by IDdrop table tabA,tabB/*id c1 c2
----------- ---------- ----------
1 a A
1 b NULL
2 c B
2 NULL C(4 row(s) affected)
*/
insert tabA
select 1, 'a' union all
select 1, 'b' union all
select 2, 'c'
create table tabB(ID int, Value varchar(10))
insert tabB
select 1, 'A' union all
select 2, 'B' union all
select 2, 'C'select ID=isnull(a.id,b.id),
VALUE1=case when (select 1 from (select id,cc=min(value) from tabb group by id) t where t.id=id and t.cc=b.value)=1 then a.value else null end,
VALUE2=case when (select 1 from (select id,cc=min(value) from taba group by id) t where t.id=id and t.cc=a.value)=1 then b.value else null end
from tabA a full join tabB b on a.id = b.id order by IDdrop table tabA,tabB/*ID VALUE1 VALUE2
----------- ---------- ----------
1 a A
1 b NULL
2 c B
2 NULL C(4 row(s) affected)
*/
insert t1
select 1, 'a' union all
select 1, 'b' union all
select 2, 'c'
create table t2(ID int, Value varchar(10))
insert t2
select 1, 'A' union all
select 2, 'B' union all
select 2, 'C'
select a.id,isnull(a.value,'')as value1,
value2=case when b.id=1 and upper(a.value)=b.value then isnull(b.value,'')end
from t1 a left join t2 b on a.id=b.id
where a.id=1
union all
select a.id, value1=case when a.id=2 and upper(b.value)<>a.value then isnull(a.value,'')
when a.id=2 and upper(b.value)=a.value then null
end,
isnull(b.value,'')as value2from t1 a left join t2 b on a.id=b.id
where a.id=2 id value1 value2
----------- ---------- ----------
1 a A
1 b NULL
2 c B
2 NULL C(4 row(s) affected)
谢谢各位!等我学习完就揭贴,还有更好的方法吗?
如果在相同id个数不大于二的情况下你们的程序是正确的,如果大于2我希望的是下面的结果:create table t1(ID int, Value varchar(10))
insert t1
select 1, 'a' union all
select 1, 'b' union all
select 1, 'd' union all
select 2, 'c'
create table t2(ID int, Value varchar(10))
insert t2
select 1, 'A' union all
select 1, 'E' union all
select 2, 'B' union all
select 2, 'C'id VALUE1 VALUE2
1 a A
1 b E
1 d NULL
2 c B
2 NULL C一句话相同的行数都取最大的那个行数,不足的补null。
怪我没有说清楚,麻烦各位了,成功揭帖我再加100分吧?
谢谢!
select id,cc=min(value) from taba group by idselect 1 from (select id,cc=min(value) from taba group by id) t where t.id=id and t.cc=a.value以上两句的用意!
当ID相同的时候 A.value 和 B.value 进行对应行配对,如果哪个表没有就 写NULL?
是这个意思吗?
我想用临时表,不过效率不高,还是等高手一条语句搞定它吧~
---创建测试环境
Declare @A Table(ID int, Value varchar(10))
Insert @A
select 1, 'a' union all
select 1, 'b' union all
select 1, 'd' union all
select 2, 'c'
Declare @B Table(ID int, Value varchar(10))
Insert @B
select 1, 'A' union all
select 1, 'E' union all
select 2, 'B' union all
select 2, 'C'Select * From @A
Select * From @B
---查询结果
Select
T.ID,
T.Value As Value1,
TT.Value As Value2
From
(Select OrdID=(Select Count(1) From @A Where ID=A.ID And Value<=A.Value),* From @A A) T
Left Join
(Select OrdID=(Select Count(1) From @B Where ID=B.ID And Value<=B.Value),* From @B B) TT
ON T.ID=TT.ID And T.OrdID=TT.OrdID
Union
Select
T.ID,
TT.Value As Value1,
T.Value As Value2
From
(Select OrdID=(Select Count(1) From @B Where ID=B.ID And Value<=B.Value),* From @B B) T
Left Join
(Select OrdID=(Select Count(1) From @A Where ID=A.ID And Value<=A.Value),* From @A A) TT
ON T.ID=TT.ID And T.OrdID=TT.OrdID
---结果
/*
ID Value1 Value2
----------- ---------- ----------
1 a A
1 b E
1 d NULL
2 NULL C
2 c B(所影响的行数为 5 行)
*/
这边的分给楼上三位给出语句的朋友,飞鸟兄请移步“
http://community.csdn.net/Expert/topic/5725/5725319.xml?temp=.1165125
接分!