根据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
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
----------
select a.aid ,b.bid ,isnull(a.Category,b.Category) as Category
from a full outer join b on a.aid =b.bid
a full join b on b.Category=a. and b.bID=a.aid
where a.Category='x'
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'
(aID Int,
Category Varchar(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
(bID Int,
Category Varchar(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
/*
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
*/
我们都要练练了...T_T
#a a full join #b b on b.Category=a.Category and b.bID=a.aid
where a.Category='x' or b.Category='x'