如何在3个表上编写连接查询 [英] How to write join query on 3 tables

问题描述

我在采访中向我询问了这个问题。



表：

学生：SID，SNMAE

书：BID，BNAME，BPRICE，SID

作者：AID，ANAME，BID



问题：

写下查询，其中列出了价格> 1000且书籍超过一位作者的学生名单。



请帮我写上面提到的查询。

Hi,

This question was asked to me in interview.

Tables:
Student: SID,SNMAE
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

Question:
Write query which give list of student who take books who's price is >1000 & Book having more than one Author.

Please help me to write above mentioned query.

推荐答案

SELECT S.SNAME
FROM Student as S
INNER JOIN Book as B
  ON S.SID=B.SID
INNER JOIN Author as A
  ON B.BID=A.BID
WHERE B.BPRICE>1000
GROUP BY A.BID,S.SNAME
HAVING COUNT(A.BID)>1

SELECT DISTINCT(s1.SNAME) FROM Student s1 JOIN BOOK b1 ON
s1.SID = b1.SID WHERE b1.BPRICE > 1000 AND EXISTS
(
SELECT COUNT(a.AID) FROM Book b2 JOIN Author a ON
b2.BID = a.BID WHERE s1.SID = b2.SID GROUP BY a.AID HAVING COUNT(a.AID) > 1
)

我想在你读完之后想一想你的问题。

i think after you reading this some idea your problem.

select * from Student st
inner join Book id on st.SID =id.sid
inner join Author at on at.bid=st.bid

为任何曲调ery点击回复。

for any query hit to reply.

这篇关于如何在3个表上编写连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！