×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

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