Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.3k views
in Technique[技术] by (71.8m points)

sql 关联查询的一个问题

有两个表,A 表又来存储图书的名称。

idname
1酒国
2红楼梦

B 表用来存储图书的别名

idaIdname
12石头记
22风月宝鉴

请教一个sql:

查询“红楼梦”的时候返回

A.idA.nameB.idB.aIdB.name
2红楼梦

查询“风月宝鉴”的是否返回

A.idA.nameB.idB.aIdB.name
2红楼梦22风月宝鉴

求各位大佬帮忙。


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

因为你描述得不是很清楚,我猜测你的场景有以下约束

  • 对于同一本书,A.name不可能在B.name中再出现
  • 你的查询每次只传入一个书名

如果上述成立,则可以这样解决,首先创建表A和B

-- drop table A;
CREATE TABLE A
(id int, 
 name varchar(30), 
 PRIMARY KEY(id));
insert into A values (1, "酒国");
insert into A values (2, "红楼梦");

-- drop table B;
create table B ( id int,
aid int,
name varchar(30),
primary key(id));
insert into B values (1,2, "石头记");
insert into B values (2,2, "风月宝鉴");  

然后通过以下SQL查询

(
select
    id as 'a.id',
    name as 'a.name',
    null as 'b.id',
    null as 'b.aid',
    null as 'b.name'
from
    a
where
    name = '某本书的真名或者别名' )

union all

(
select 
    * 
from 
    a inner join b 
on 
    a.id=b.aid
where 
    b.name = '某本书的真名或者别名'
)

则传入的名字为红楼梦时结果为
图片.png

传入的名字为风月宝鉴时结果为
图片.png

以上代码在mysql8中测试通过


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

2.1m questions

2.1m answers

60 comments

57.0k users

...