This topic has been archived. It cannot be replied.
-
工作学习 / 学科技术讨论 / 求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
-xxu99(春眠不觉晓);
2011-10-27
{495}
(#7018860@0)
-
如果是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;
-newkid(newkid);
2011-10-27
{254}
(#7019115@0)
-
try thisselect 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
-ssy214(村长);
2011-10-27
{227}
(#7019188@0)
-
忘了说是在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)
-
my answer
-c1xwy(洪兴罩俺去战斗);
2011-11-9
{718}
(#7054185@0)