×

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

If you use SQL 2005 or higher, try

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 )
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / 求query. Thanks.
    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见内
      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
      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上.谢谢楼上二位。
    • If you use SQL 2005 or higher, try
      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
      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)