求两表查询语句（优化的）
根据Category查询a，b两表，要求Category相同，查询出相同Category（都为‘x’）的aID和bID要求
1、aID中aCheck 为r，bID不存在；bID存在 a表中不存在相同的aID,或者存在相同的aID但是aCheck不是‘r’
2、a表中aID等于b表中bID切aCheck为r（怎么样写效率最好）结果d
我写的：
select aid,aCheck,bid from a left join b on aID=bID and a.Category=b.Category where a.Category= 'x ' and a.Check= 'r ' and aID in(select bID from b where b.Category= '123 ')
a: b:
aID Category aCheck bID Category
1 x r 1 x
2 x w 2 x
3 x w 3 x
4 x w 7 x
5 x r 8 x
6 x r 9 x
9 y r 10 y
11 y r 11 y
得到表
结果c：
aID aCheck bID Category
5 r null x
6 r null x
2 w 2 x
3 w 3 x
null null 7 x
null null 8 x
null null 9 x
结果d：
aID aCheck bID Category
1 r 1 x
[解决办法]
--1.
Create Table a
(aIDInt,
CategoryVarchar(10),
aCheckVarchar(10))
Insert a Select 1, 'x ', 'r '
Union All Select 2, 'x ', 'w '
Union All Select 3, 'x ', 'w '
Union All Select 4, 'x ', 'w '
Union All Select 5, 'x ', 'r '
Union All Select 6, 'x ', 'r '
Union All Select 9, 'y ', 'r '
Union All Select 11, 'y ', 'r '
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,
A.aCheck,
B.bID,
A.Category
From
A
Left Join
B
On A.aID = B.bID And B.Category = 'x '
Where B.bID Is Null And A.Category = 'x ' And A.aCheck = 'r '
Union All
Select
A.aID,
A.aCheck,
B.bID,
B.Category
From
B
Left Join
A
On A.aID = B.bID And A.Category = 'x '
Where (A.aID Is Null And B.Category = 'x ') Or (A.aID Is Not Null And A.aCheck != 'r ')
GO
Drop Table a, b
--Result
/*
aIDaCheckbIDCategory
5rNULLx
6rNULLx
2w2x
3w3x
NULLNULL7x
NULLNULL8x
NULLNULL9x
*/
[解决办法]
兼于表联结操作要耗费大量的时间,所以最后不要用连接操作...
select AId,BId from a,b where a.Category=b.Category and a.AId=b.BId
and a.Category= 'x '
and a.ACheck= 'r ' and AId in(select BId from b)
这样也可以达到你的要求..
