表a字段
id pid des
1 1 f
2 3 x
2 4 c
表b字段
pid des
1 a
2 b
3 c
4 c 我需要得到两张表的联合查询结果如下
表c id pid des
1 1 f
1 2 null
1 3 null
1 4 null
2 1 null
2 2 null
2 3 x
2 4 c
id pid des
1 1 f
2 3 x
2 4 c
表b字段
pid des
1 a
2 b
3 c
4 c 我需要得到两张表的联合查询结果如下
表c id pid des
1 1 f
1 2 null
1 3 null
1 4 null
2 1 null
2 2 null
2 3 x
2 4 c
from (
select id,pid
from (
select distinct id from a
) as x cross join (
select distinct pid from b
) as y
) as a1 left join a a2
where a1.id=a2.id
and a1.pid=a2.pid
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'where'.
但是,得到的结果却与我原来做的查询一样,是针对每一个A表的数据都行到一个补集
这是我原来写的
SELECT d.des AS Expr1, c.id AS Expr2, c.pidFROM (SELECT dbo.a.id, dbo.b.pid FROM dbo.a CROSS JOIN dbo.b) c LEFT OUTER JOIN (SELECT a.id, b.pid, a.des FROM a, b WHERE b.pid = a.pid) d ON c.id = d.id AND c.pid = d.pid我需要的是这样的
id pid des
1 1 f
1 2 null
1 3 null
1 4 null
2 1 null
2 2 null
2 3 x
2 4 c而现在得到的是这样的
id pid des
----------- ----------- --------------------------------------------------
1 1 aa
1 2 NULL
1 3 NULL
1 4 NULL
2 1 NULL
2 2 bb
2 3 NULL
2 4 NULL
3 1 NULL
3 2 cc
3 3 NULL
3 4 NULL
4 1 NULL
4 2 dd
4 3 NULL
4 4 NULL(16 row(s) affected)
from (
select id,pid
from (
select distinct id from a
) as x cross join (
select distinct pid from b
) as y
) as a1 left join a a2
on a1.id=a2.id
and a1.pid=a2.pid
order by a1.id這樣就行了
id pid des
----------- ----------- --------------------------------------------------
1 1 f
1 2 NULL
1 3 NULL
1 4 NULL
2 1 NULL
2 2 NULL
2 3 x
2 4 NULL
3 1 NULL
3 2 NULL
3 3 NULL
3 4 c(12 row(s) affected)
id pid des
1 1 f
2 3 x
2 4 c 还是表a字段
id pid des
1 1 f
2 3 x
3 4 c 如果是后者,你说明一下你的结果需要怎么产生的