多表不同字段联合查询？有挑战性！

[gs8716]

有三个表A、B、C
A表字段
Aid Bid Cid .....(其它字段)

B表字段
Bid BName

C表字段
Cid CName

查询结果中要有字段 Bid,Cid,BName,CName 最后两个字段模糊查询是或(or)的关系
我以前的想法，但通不过，出错了。

select top 10 * from (

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid where B.BName like '%key%'

union all

select *,row_number() over(order by Aid desc) as row from A
inner join C on A.Cid=C.Cid where C.CName like '%key%'

) as t
where row between 1 and 10
order by Aid desc

求助！！！

改成这样的之后，结果全部是重复的

select top 10 * from (

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid

inner join C on A.Cid=C.Cid 

where C.CName like '%key%' or B.BName like '%key%'

) as t
where row between 1 and 10
order by Aid desc

要注意查询效率。

[gs8716]

[Quote=引用 6 楼 hch126163 的回复:]
select top 10 * from (

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid
inner join C on A.Cid=C.Cid
where B.BName like '%key%'

union

select *,row_……
[/Quote]

应该用union all 你这种写法可能效率不高，会检索没必要的表。

[天下如山]

if object_id('aa') is not null drop table aa
create table aa(aid char(10),bid char(10),cid char(10))
if object_id('bb') is not null drop table bb
create table bb(bid char(10),bname char(30))
if object_id('cc') is not null drop table cc
create table cc(cid char(10),cname char(30))
insert into aa
select '1','1','1' union all
select '2','2','3' union all
select '3','4','1' union all
select '4','2','2' union all
select '5','3','1' union all
select '6','1','4' union all
select '7','1','5'
select * from aa
insert into bb
select '4','ddd' union all
select '2','bbb' union all
select '3','ccc'
update bb set bname='bkey' where bid=2 or bid=3
insert into cc
select '1','caaa' union all
select '2','cbbb' union all
select '3','cccc' union all
select '4','cddd'
update cc set cname='ckey' where cid=1 or cid=4
with bbs as
(
select a.aid,b.bid,b.bname,c.cid,c.cname from aa a join bb b on a.bid=b.bid join cc c on a.cid=c.cid
)
select * from bbs a where exists(select 1 from bbs where a.aid=bbs.aid and a.bname like '%key%' or a.cname like '%key%')
/*
aid        bid        bname         cid        cname
1          1          aaa            1          ckey                          
6          1          aaa            4          ckey                          
2          2          ckey           3          cccc                          
4          2          ckey           2          cbbb                          
5          3          ckey           1          ckey                          
3          4          ddd            1          ckey  
*/

[天下如山]

"求助！！！
改成这样的之后，结果全部是重复的"
按照你所说的 会得到重复的值 我所做的是先3表联合查询得到数据集
然后用CTE 里面再进行或'like'判断得到 你所需要的数据集。

[xuhaibobishen]

select aa.*,bb.*,cc.* from aa join bb on aa.id=bb.cid and bb.adddate like '%%' join cc on aa.id=cc.cid and cc.cname like '%%'

[xuhaibobishen]

表名没和你的对应上，你对着套一下

[show123456]

select top 10 * from (

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid

inner join C on A.Cid=C.Cid 

where C.CName like '%key%' or B.BName like '%key%'

) as t
where row between 1 and 10
order by Aid desc

我测试过这个可以是正确的。可能是你的数据有问题。

[gs8716]

[Quote=引用 1 楼 xuhaibobishen 的回复:]
select aa.*,bb.*,cc.* from aa join bb on aa.id=bb.cid and bb.adddate like '%%' join cc on aa.id=cc.cid and cc.cname like '%%'
[/Quote]

你的例子是且的关系，我需要的是或 BName='key' 或 CName='key'

[hch126163]

select top 10 * from (

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid
inner join C on A.Cid=C.Cid
where B.BName like '%key%'

union

select *,row_number() over(order by Aid desc) as row from A
inner join B on A.Bid=B.Bid
inner join C on A.Cid=C.Cid
where C.CName like '%key%'

) as t
where row between 1 and 10
order by Aid desc