求一个sql，表如图

[siemens_chang]

表1
表2
表3
查询结果
其中表1的id分别和表2、表3的cid对应，表2和表三没有任何联系。就是想把三个表分别cname和sname和tname三列“拼接”起来的意思

[shoppo0505]

select t1.cname, t2.sname ,t3tname from t1 inner join t2 on t1.id = t2.cid inner join t3 on t1.id = t3.cid

[shoppo0505]

两个都用left outer join

[發糞塗牆]

你这个搞不出null值的哦

[xxfvba]

--2005以上版本，比较笨的办法 select case when a.cname is null then b.cname else a.cname end as cname,a.sname,b.tname from (select * from t1 , (select * ,rn=ROW_NUMBER() over(partition by cid order by sname) from t2) t4 where t1.ID=t4.cid) a full join (select * from t1,(select *,rn=ROW_NUMBER() over (partition by cid order by tname) from t3) t5 where t1.ID=t5.cid) b on a.cid=b.cid and a.rn=b.rn

[save4me]

参考楼上的，清除他语句里面因为用*取所有列导致的多个id问题 其中c为第一个表，s为第二个表，t为第三个表

SELECT (CASE
	WHEN S0.cname IS NULL THEN T0.cname
	ELSE S0.cname
	END) AS cname
    , S0.sname
    , T0.tname
FROM (SELECT c.id
		, c.cname
		, S1.cid
		, S1.sname
		, S1.rn
      FROM c
          , (SELECT *
                 , rn = ROW_NUMBER () OVER (PARTITION BY cid ORDER BY sname)
             FROM s) S1
      WHERE c.ID = S1.cid) S0
    FULL JOIN
    (SELECT c.id
    	, c.cname
    	, T1.cid
    	, T1.tname
    	, T1.rn
	  FROM c
	       , (SELECT *
	              , rn = ROW_NUMBER () OVER (PARTITION BY cid ORDER BY tname)
	          FROM t) T1
	   WHERE c.ID = T1.cid) T0
	ON S0.cid = T0.cid AND S0.rn = T0.rn

[siemens_chang]

引用 4 楼 xxfvba 的回复:

--2005以上版本，比较笨的办法 select case when a.cname is null then b.cname else a.cname end as cname,a.sname,b.tname from (select * from t1 , (select * ,rn=ROW_NUMBER() over(partition by cid order by sname) from t2) t4 where t1.ID=t4.cid) a full join (select * from t1,(select *,rn=ROW_NUMBER() over (partition by cid order by tname) from t3) t5 where t1.ID=t5.cid) b on a.cid=b.cid and a.rn=b.rn

可以出来，但限制有点大。不过谢谢

[siemens_chang]

引用 5 楼 save4me 的回复:

参考楼上的，清除他语句里面因为用*取所有列导致的多个id问题 其中c为第一个表，s为第二个表，t为第三个表

SELECT (CASE
	WHEN S0.cname IS NULL THEN T0.cname
	ELSE S0.cname
	END) AS cname
    , S0.sname
    , T0.tname
FROM (SELECT c.id
		, c.cname
		, S1.cid
		, S1.sname
		, S1.rn
      FROM c
          , (SELECT *
                 , rn = ROW_NUMBER () OVER (PARTITION BY cid ORDER BY sname)
             FROM s) S1
      WHERE c.ID = S1.cid) S0
    FULL JOIN
    (SELECT c.id
    	, c.cname
    	, T1.cid
    	, T1.tname
    	, T1.rn
	  FROM c
	       , (SELECT *
	              , rn = ROW_NUMBER () OVER (PARTITION BY cid ORDER BY tname)
	          FROM t) T1
	   WHERE c.ID = T1.cid) T0
	ON S0.cid = T0.cid AND S0.rn = T0.rn

Good!