求一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