This website requires Javascript to function properly. Please go to the setting of your web browser and enable Javascript for this website.

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

Ad by

最优利率和cashback可以申请特批，好信用好收入offer更好。请点链接扫码加微信咨询，Scotiabank -- Nick Zhang 6478812600。

If you use SQL 2005 or higher, try

deep_blue(BLUE)

Select Case when B.ID IS NULL then A.ID else B.ID END ID, A.ColA, A.ColB, B.colX, B.colY
from
(Select ID, (Select ID From (Select ROW_NUMBER() over(order by ID) row,* from TA) A where A.row = temA.row +1) nextID, ColA, ColB from
(Select ROW_NUMBER() over(order by ID) row,* from TA) temA ) A
LEFT join TB B ON A.ID = B.ID
union
Select Case when B.ID IS NULL then A.ID else B.ID END ID, A.ColA, A.ColB, B.colX, B.colY
from
(Select ID, (Select ID From (Select ROW_NUMBER() over(order by ID) row,* from TA) A where A.row = temA.row +1) nextID, ColA, ColB from
(Select ROW_NUMBER() over(order by ID) row,* from TA) temA ) A
join TB B
ON (B.ID >A.ID AND B.ID < A.nextID ) OR (A.nextID IS NULL AND B.ID >= A.ID )

(#7054007@0)
Last Updated: 2011-11-9
This post has been archived. It cannot be replied.

Report

Replies, comments and Discussions:

工作学习 / 学科技术讨论 / 求query. Thanks. -xxu99(春眠不觉晓); 2011-10-27 {495} (#7018860@0)
I have two tables and hope to generate the 3rd table. The sample tables are:

Table A:
ID colA colB
1 AAA BBBBB
3 CCC DDDDD
8 EEE FFFFF

Table B:
ID colX colY
2 aaa bbbbb
4 ccc ddddd
5 sssss d
6 ffff ddd
7 adf fghs
9 ewt qt

Result Table C:
ID colA colB colX colY
1 AAA BBBBB
2 AAA BBBBB aaa bbbbb
3 CCC DDDDD
4 CCC DDDDD ccc ddddd
5 CCC DDDDD sssss d
6 CCC DDDDD ffff ddd
7 CCC DDDDD adf fghs
8 EEE FFFFF
9 EEE FFFFF ewt qt

如果是ORACLE见内 -newkid(newkid); 2011-10-27 {254} (#7019115@0)
CREATE TABLE C AS
SELECT NVL(A.id,b.id)
,LAST_VALUE(cola IGNORE NULLS) OVER(ORDER BY NVL(A.id,b.id)) AS cola
,LAST_VALUE(colb IGNORE NULLS) OVER(ORDER BY NVL(A.id,b.id)) AS colb
,colx
,coly
FROM A FULL JOIN B ON a.id=b.id;
try this -ssy214(村长); 2011-10-27 {227} (#7019188@0)
select B.ID,A.ColA,A.ColB,B.ColX, B.colY FROM B
INNER JOIN
(SELECT B.ID,(SELECT Max(ID) FROM A WHERE ID<=B.ID) AS AID FROM B) C
ON B.ID=C.ID
LEFT Outer join A
ON A.ID=C.AID
UNION
select *, '','' FROM A
Order by ID
忘了说是在SQL Server上.谢谢楼上二位。 -xxu99(春眠不觉晓); 2011-10-27 (#7019654@0)
If you use SQL 2005 or higher, try -deep_blue(BLUE); 2011-11-9 {730} (#7054007@0)
Select Case when B.ID IS NULL then A.ID else B.ID END ID, A.ColA, A.ColB, B.colX, B.colY
from
(Select ID, (Select ID From (Select ROW_NUMBER() over(order by ID) row,* from TA) A where A.row = temA.row +1) nextID, ColA, ColB from
(Select ROW_NUMBER() over(order by ID) row,* from TA) temA ) A
LEFT join TB B ON A.ID = B.ID
union
Select Case when B.ID IS NULL then A.ID else B.ID END ID, A.ColA, A.ColB, B.colX, B.colY
from
(Select ID, (Select ID From (Select ROW_NUMBER() over(order by ID) row,* from TA) A where A.row = temA.row +1) nextID, ColA, ColB from
(Select ROW_NUMBER() over(order by ID) row,* from TA) temA ) A
join TB B
ON (B.ID >A.ID AND B.ID < A.nextID ) OR (A.nextID IS NULL AND B.ID >= A.ID )
my answer -c1xwy(洪兴罩俺去战斗); 2011-11-9 {718} (#7054185@0)
drop table #A
drop table #B

select 1 ID, 'AAA' colA, 'BBBBB' colB into #A
union
select 3, 'CCC', 'DDDDD'
union
select 5, 'DDD', 'FFF'
union
select 8, 'EEE', 'FFFFF'

select 2 ID, 'aaa' colX, 'bbbbb' colY into #B
union
select 4, 'ccc', 'ddddd'
union
select 5, 'sssss', 'd'
union
select 6, 'ffff', 'ddd'
union
select 7, 'adf', 'fghs'
union
select 9, 'ewt', 'qt'

select * from #A
select * from #B

select B.ID, colA, colB, colX, colY
from #A A,
(
select *, (select top 1 ID from #A where ID <= #B.ID order by ID desc) aID
from #B
) B
where A.ID = B.aID
union
select ID, colA, colB, null, null
from #A
where ID not in (select ID from #B)

@Kingston

If you use SQL 2005 or higher, try

Replies, comments and Discussions:

More Topics