ÇóÒ»SQL£¿ - MS-SQL Server / Ó¦ÓÃʵÀý

SQL code:

Êý¾Ý½á¹¹£º
±ía,
ida,name,
1 a
2 b
3 c
4 d
±íb,
idb,ida,post
1 1 r
2 1 s
5 3 q
6 4 b
7 5 n
±ía Óë±íb¹ØÁª×Ö¶ÎΪ a.ida = b.ida
ÇóÒ»ÌõsqlÓï¾äÒªÇó²éѯµÄ½á¹ûΪһÏ£º
ida name idb post
1 a 1 r
1 a 2 s
2 b
3 c 5 q
4 d 6 b
7 n

---

SQL code:


select
a.ida, a.name, b.idb, b.post
from
a full outer join b on
a.ida=b.ida

---

SQL code:


CREATE TABLE a(ida int,NAME VARCHAR(1))
CREATE TABLE b(idb INT,ida INT,post VARCHAR(1))
INSERT a
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c' UNION ALL
SELECT 4,'d'

INSERT b
SELECT 1,1,'r' UNION ALL
SELECT 2,1,'s' UNION ALL
SELECT 5,3,'q' UNION ALL
SELECT 6,4,'b' UNION ALL
SELECT 7,5,'n'

SELECT ISNULL(CAST(a.ida AS VARCHAR),''),ISNULL(a.name,'') AS name,ISNULL(CAST(b.idb AS VARCHAR),'') AS idb,ISNULL(b.post,'') AS post
from a LEFT JOIN
b ON a.ida=b.ida
UNION
SELECT ISNULL(CAST(a.ida AS VARCHAR),''),ISNULL(a.name,''),ISNULL(CAST(b.idb AS VARCHAR),'') AS idb,ISNULL(b