求一sql语句两表查询
根据Category查询a，b两表，要求Category相同，aID和bID相同的放同一行，aID有的bID没有就bID为空，bID有的aID没有，就aID为空，如下查询Category为x的数据
a: b:
aID Category bID Category
1 x 1 x
2 x 2 x
3 x 3 x
4 x 7 x
5 x 8 x
6 x 9 x
9 y 10 y
11 y 11 y
得到表
c：
aID bID Category
1 1 x
2 2 x
3 3 x
4 null x
5 null x
6 null x
null 7 x
null 8 x
null 9 x
[解决办法]
drop table a,b
go
create table a(aID int,Category char(1))
insert into a
select 1, 'x '
union all select 2, 'x '
union all select 3, 'x '
union all select 4, 'x '
union all select 5, 'x '
union all select 6, 'x '
union all select 9, 'y '
union all select 11, 'y '
create table b(bID int,Category char(1))
insert into b
select 1, 'x '
union all select 2, 'x '
union all select 3, 'x '
union all select 7, 'x '
union all select 8, 'x '
union all select 9, 'x '
union all select 10, 'y '
union all select 11, 'y '
select aID,bID,a.Category
from a
left join b on a.aID=b.bID and a.Category=b.Category
where a.Category= 'x '
union all
select aID,bID,b.Category
from b
left join a on a.aID=b.bID and a.Category=b.Category
where b.Category= 'x ' and not exists(select 1 from a aa where aa.aID=b.bID and aa.Category=b.Category)
/*
aID bID Category
----------- ----------- --------
1 1 x
2 2 x
3 3 x
4 NULL x
5 NULL x
6 NULL x
NULL 7 x
NULL 8 x
NULL 9 x
（所影响的行数为 9 行）
*/
[解决办法]
看来我还没到空写的水平.....
create table #a(aID int , Category varchar(10) )
insert into #a select 1, 'x '
union all select 2, 'x '
union all select 3, 'x '
union all select 4, 'x '
union all select 5, 'x '
union all select 6, 'x '
union all select 9, 'y '
union all select 11, 'y '
create table #b(bID int, Category varchar(10))
insert into #b select 1, 'x '
union all select 2, 'x '
union all select 3, 'x '
union all select 7, 'x '
union all select 8, 'x '
union all select 9, 'x '
union all select 10, 'y '
union all select 11, 'y '
select a.aid,b.bid,isnull(a.Category,b.Category) from
#a a full join #b b on b.Category=a.Category and b.bID=a.aid
where a.Category= 'x ' or b.Category= 'x '
[解决办法]
直接用full join
Create Table a
(aIDInt,
CategoryVarchar(10))
Insert a Select 1, 'x '
Union All Select 2, 'x '
Union All Select 3, 'x '
Union All Select 4, 'x '
Union All Select 5, 'x '
Union All Select 6, 'x '
Union All Select 9, 'y '
Union All Select 11, 'y '
Create Table b
(bIDInt,
CategoryVarchar(10))
Insert b Select 1, 'x '
Union All Select 2, 'x '
Union All Select 3, 'x '
Union All Select 7, 'x '
Union All Select 8, 'x '
Union All Select 9, 'x '
Union All Select 10, 'y '
Union All Select 11, 'y '
GO
Select
A.aID,
B.bID,
IsNull(A.Category, B.Category) As Category
From
A
Full Join
B
On A.aID = B.bID And A.Category = B.Category
Where IsNull(A.Category, B.Category) = 'x '
Order By IsNull(A.aID, B.bID)
GO
Drop Table A, B
--Result
/*
aIDbIDCategory
11x
22x
33x
4NULLx
5NULLx
6NULLx
NULL7x
NULL8x
NULL9x
*/
