多表查询（嵌套查询）

最新推荐文章于 2024-04-18 22:35:20 发布

qiuzhimin0

最新推荐文章于 2024-04-18 22:35:20 发布

阅读量2k

点赞数 2

文章标签： sql

本文链接：https://blog.csdn.net/qiuzhimin0/article/details/104299769

版权

----如果查询涉及到多张表，选用嵌套查询还是连接查询？
--1.如果查询嵌套和连接都能实现，嵌套查询效率高
--2.在很多查询中，只能用连接嵌套无法实现功能（查询的内容在多个表中）

---=====嵌套查询 =================
---查询出陈鹏借了哪些书

--Student--SNO--->Borrowbook-----BookId---->Book
Select BookName
From LibraryDB.dbo.Book
Where BookId In
(
   Select BookId
   From LibraryDB.dbo.BorrowBook
   Where SNO =
   (
       Select SNO
       From LibraryDB.dbo.Student
       Where SName='陈鹏'
   )
)
---查询出借的最多的那本书的作者

--条件： BorrowBook ---BookId--->Book----AuthorID---->AuthorName

Select AuthorName
From LibraryDB.dbo.Author
Where AuthorID In
(
   Select BookAuthor
   From LibraryDB.dbo.Book
   Where BookId In
   (
       Select BookId
       From LibraryDB.dbo.borrowBook
       Group by BookId
       Having count(*)=
       (
           Select Top 1 Count(*)
           from LibraryDB.dbo.BorrowBook
           Group by BookId
           Order by Count(*) DESC
       )
   )
)

---查询出计算机书中借的最多的那本书的作者的电话
Select AuthorTelNo
From Author
Where AuthorID IN
(
   Select BookAuthor
   from LibraryDB.dbo.Book
   Where BookID in
   (
       Select BookId
       From (
           Select T3.BookID As bookId

from LibraryDB.dbo.BookType As T1 Inner Join LibraryDB.dbo.Book As T2 On T1.ID=T2.BookTypeID
Inner Join LibraryDB.dbo.BorrowBook As T3 on T2.BookID=T3.BookID
           where T1.TypeName='计算机'
       ) AS T5
       Group by BookId
       Having count(*)=
       (
           Select Top 1 count(*)
           From
           (
               Select T3.BookID As bookId

from LibraryDB.dbo.BookType As T1 Inner Join LibraryDB.dbo.Book As T2 On T1.ID=T2.BookTypeID
Inner Join LibraryDB.dbo.BorrowBook As T3 on T2.BookID=T3.BookID
               where T1.TypeName='计算机'
           ) AS T4
           Group by bookId
           Order by Count(*) DESC
       )
   )
)

----In,Exists,Any,some,All
---查询出借过书的学员姓名
Select SNo,SName
from LibraryDB.dbo.Student
Where SNo = any
(
Select SNo
From LibraryDb.dbo.BorrowBook
)

---------------------------------------------

Select SNo,SName
From LibraryDB.dbo.Student
Where Exists
(
   Select *
   From LibraryDB.dbo.BorrowBook
   Where LibraryDB.dbo.Student.SNO=LibraryDB.dbo.BorrowBook.SNO
)

--查询出年龄最大学生学号和姓名

Select *
from LibraryDb.dbo.Student
where SAGE>=All
(
Select IsNull(Sage,0)
from LibraryDB.dbo.student
)

--统计出每一类书中的最高的价格

Select TypeName AS '类别名称',MaxPrice As '最高价格'
From
   (
       Select BookTypeId,max(Bookprice) As 'MaxPrice'
       from LibraryDB.dbo.Book
       Group By BookTypeID
   ) AS T1,LibraryDB.dbo.BookType AS T2
Where T1.BookTypeID=T2.ID

--查询出借的最多的书的ID号
Select BookName
From LibraryDB.dbo.Book
Where BookId IN
(
   Select BookId
   from LibraryDB.dbo.BorrowBook
   Group by BookId
   Having Count(*)=
   (
       Select Top 1 Count(*)
       from LibraryDB.dbo.BorrowBook
       Group by BookId
       order By Count(*) DESC
   )
)

-- 统计出借书多于两本的学生姓名以及数量，按照数量的降序排列
---连接查询

Select T1.SNO As '学号',SName As '姓名',BorrowNumber As '借书数量'
From
(
   Select SNO,count(*) As BorrowNumber
   From LibraryDB.dbo.BorrowBook
   Group by SNO
   Having Count(*)>2
) AS T1, LibraryDB.dbo.Student AS T2
Where T1.SNO=T2.SNO

---统计出第一季度洗衣机销售总量大于12000的区域，按照销量的降序排列

Select PArea As '区域',Pname AS '名称',sum(PSaleNO) As '销量'
from LibraryDB.dbo.SalesTable
Where PName='洗衣机' ---在分组之前筛选记录
Group by PArea,Pname
Having sum(PSaleNO)>12000 ---对于分组之后的结果进行筛选
Order by sum(PSaleNO) DESC