有下面两个表A表
ID CDOE
48 12.000.000.001
49 12.000.000.002B表ID CODE
50 12.000.000.002
49 12.000.000.001
48 11.000.000.0001两个表有关联的字段即是ID.B表 ID 48对应A表ID为48的 12.000.000.001,B表的49对应A表ID为48的12.000.000.002那么就是B表 ID 48包括了 A表ID为48的12.000.000.001 和 A表ID为48的12.000.000.002要求显示结果表如下
ID B表ID B表ID CODE
48 48 11.000.000.0001
48 48 49 12.000.000.001
48 49 50 12.000.000.002
ID CDOE
48 12.000.000.001
49 12.000.000.002B表ID CODE
50 12.000.000.002
49 12.000.000.001
48 11.000.000.0001两个表有关联的字段即是ID.B表 ID 48对应A表ID为48的 12.000.000.001,B表的49对应A表ID为48的12.000.000.002那么就是B表 ID 48包括了 A表ID为48的12.000.000.001 和 A表ID为48的12.000.000.002要求显示结果表如下
ID B表ID B表ID CODE
48 48 11.000.000.0001
48 48 49 12.000.000.001
48 49 50 12.000.000.002
ID B表ID B表ID CODE
48 null 48 11.000.000.0001
48 48 49 12.000.000.001
48 49 50 12.000.000.002
--A表
SELECT ID = 48,CDOE ='12.000.000.001' INTO A
UNION ALL SELECT 49,'12.000.000.002'--B表
SELECT ID = 50,CDOE ='12.000.000.002' INTO B
UNION ALL SELECT 49,'12.000.000.001'
UNION ALL SELECT 48,'12.000.000.0001';with t as
(select a.id aid,b.id bid,b.cdoe from A a, B b)
select aid,c.id,bid,t.cdoe from t left join A c on c.cdoe = t.cdoe
order by aid,bid
/*
aid id bid cdoe
----------- ----------- ----------- ---------------
48 NULL 48 12.000.000.0001
48 48 49 12.000.000.001
48 49 50 12.000.000.002
49 NULL 48 12.000.000.0001
49 48 49 12.000.000.001
49 49 50 12.000.000.002(6 row(s) affected)
*/
--A表
SELECT ID = 48,CDOE ='12.000.000.001' INTO A
UNION ALL SELECT 49,'11.000.000.002'--B表
SELECT ID = 50,CDOE ='12.000.000.002' INTO B
UNION ALL SELECT 49,'12.000.000.001'
UNION ALL SELECT 48,'11.000.000.0001';with t as
(select a.id aid,b.id bid,b.cdoe from A a, B b)
select aid,c.id,bid,t.cdoe from t left join A c on c.cdoe = t.cdoe
order by aid,bid
/*
aid id bid cdoe
----------- ----------- ----------- ---------------
48 NULL 48 11.000.000.0001
48 48 49 12.000.000.001
48 NULL 50 12.000.000.002
49 NULL 48 11.000.000.0001
49 48 49 12.000.000.001
49 NULL 50 12.000.000.002(6 row(s) affected)
*/