SELECT 0 AS SNO,0 AS SPRICE,'' AS SNAME,T12.SNAME2
FROM (SELECT * FROM T3 Order by t3.sno2) T12
UNION
SELECT T11.SNO,T2.SPRICE,T11.SNAME,T11.SNAME2
FROM (SELECT * FROM T1 ,T3 Order by T1.SNo,t3.sno2) T11,
T2
WHERE T11.SNO = T2.SNO(+) AND T11.SNO2 = T2.SNO2(+)
SQL:
SELECT 0 AS SNO,0 AS SPRICE,'' AS SNAME,T11.SNAME2
FROM (SELECT * FROM T1 ,T3 Order by T1.SNo,t3.sno2) T11
UNION
SELECT T11.SNO,T2.SPRICE,T11.SNAME,T11.SNAME2
FROM (SELECT * FROM T1 ,T3 Order by T1.SNo,t3.sno2) T11,
T2
WHERE T11.SNO = T2.SNO(+) AND T11.SNO2 = T2.SNO2(+)
结果:
SNO SPRICE SNAME SNAME2
0 0 (NULL) Bei
0 0 (NULL) Dong
0 0 (NULL) Nan
0 0 (NULL) Xi
1 100 Name1 Dong
1 110 Name1 Nan
1 (NULL) Name1 Bei
1 (NULL) Name1 Xi
2 210 Name2 Dong
2 230 Name2 Xi
2 (NULL) Name2 Bei
2 (NULL) Name2 Nan
3 320 Name3 Nan
3 (NULL) Name3 Bei
3 (NULL) Name3 Dong
3 (NULL) Name3 Xi
SELECT T11.ID,T2.price,T11.name,T11.providerName
FROM (SELECT * FROM goods ,provider Order by goods.id,provider.providerId ) T11,
price T2
where T11.id = T2.id(+) AND T11.providerId = T2.provider(+)
上面的方法3,假如你不想添加,可以用下面的SQL句子。
select 0,0,T11.name,T11.providerName
FROM (SELECT * FROM goods ,provider Order by goods.id,provider.providerId ) T11
UNION
SELECT T11.ID,T2.price,T11.name,T11.providerName
FROM (SELECT * FROM goods ,provider Order by goods.id,provider.providerId ) T11,
price T2
where T11.id = T2.id(+) AND T11.providerId = T2.provider(+)