3.子查询

大家好，欢迎来到IT知识分享网。

1、查询从来没有借过书的读者姓名。（分别使用 not in、not exists和连接查询三种写法实现，体会写法的不同之处）

--方式1: not in
SELECT readerName FROM Reader 
WHERE readerNo not in (SELECT readerNo FROM Borrow );
--方式2: not exists
SELECT readerName FROM Reader a
WHERE  NOT EXISTS(SELECT * FROM Borrow b WHERE a.readerNo = b.readerNo);
--方式3: 连接查询
SELECT a.readerName  
FROM Reader a LEFT JOIN Borrow b
ON a.readerNo = b.readerNo
where b.readerNo IS null;

2、查询目前没有在借“经济类”图书的读者编号、读者姓名和出生日期。（分别使用 not in、not exists和派生表查询三种写法实现，体会写法的不同之处）

--方式1: not in

SELECT readerNo,readerName,SUBSTRING(identifycard, 7, 8) AS birthday
FROM Reader 
WHERE readerNo NOT IN(
			SELECT readerNo FROM Borrow 
			WHERE bookNo in (
				SELECT bookNo FROM Book 
				WHERE classNo =(
					SELECT classNo FROM BookClass  
					WHERE className = '经济类')
			)
	GROUP BY readerNo
)
--方式2: not exists
SELECT r.readerNo, r.readerName, SUBSTRING(r.identifycard, 7, 8) AS birthday 
FROM Reader r
WHERE NOT EXISTS (
  SELECT 1
  FROM Borrow b
  INNER JOIN Book b2 ON b.bookNo = b2.bookNo
  INNER JOIN bookClass bc ON b2.classNo = bc.classNo
  WHERE b.readerNo = r.ReaderNo
    AND bc.className = '经济类'
);
--方式3: 派生表
SELECT r.ReaderNo, r.ReaderName, SUBSTRING(r.identifycard, 7, 8) AS birthday 
FROM Reader r
WHERE r.readerNo NOT IN (
  SELECT readerNo
  FROM (
    SELECT b.readerNo, b2.classNo
    FROM Borrow b
    INNER JOIN Book b2 ON b.bookNo = b2.bookNo
  ) AS borrow_class
  INNER JOIN BookClass bc ON borrow_class.classNo = bc.classNo
  WHERE bc.className = '经济类'
);
--方法4:多表查询
SELECT a.readerNo,a.readerName,SUBSTRING(a.identifycard, 7, 8) AS birthday
FROM Reader a,Borrow b,Book c 
WHERE  a.readerNo = b.readerNo
AND b.bookNo = c.bookNo
AND c.classNo not in (
						SELECT b.classNo FROM Book a LEFT JOIN BookClass b 
						ON a.classNo = b.classNo
						WHERE b.className = '经济类')
GROUP BY a.readerNo,a.readerName,a.identifycard;

3、借阅过政治经济学图书有借阅过数据库系统概念图书的读者编号和读者姓名。
政治经济学’,’数据库系统概念’)

1.查询借阅过政治经济学图书的读者编号和读者姓名

2.查询借阅过数据库系统概念图书的读者编号和读者姓名

SELECT b.readerNo,b.readerName
FROM Borrow a LEFT JOIN Reader b
ON a.readerNo = b.readerNo
WHERE a.readerNo IN(
			SELECT readerNo FROM book a LEFT JOIN Borrow b
			ON a.bookNo = b.bookNo 
			WHERE a.bookName = '政治经济学' OR a.bookName = '数据库系统概念')
GROUP BY b.readerNo,b.readerName;

4、查询既借过“政治经济学”图书又借过“数据库系统概论”图书的读者编号、读者姓名以及这两种图书的名称、借书日期和归还日期。(分别使用in和union两种方法实现，体会写法的不同)
第一种方法,使用使用in

#第一种方法,使用使用in
SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate 
FROM Reader a,Book b,Borrow c 
WHERE a.readerNo = c.readerNo 
AND b.bookNo = c.bookNo 
AND b.bookName IN('政治经济学','数据库系统概念')
AND a.readerNo IN(
		SELECT a.readerNo FROM Borrow a,Book b 
		WHERE a.bookNo = b.bookNo 
		AND b.bookName = '政治经济学'
		AND a.readerNo IN(
					SELECT a.readerNo FROM Borrow a,Book b 
					WHERE a.bookNo = b.bookNo 
					AND b.bookName = '数据库系统概念')
		)
#第二种方法,使用使用union
SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate 
FROM Reader a,Book b,Borrow c 
WHERE a.readerNo = c.readerNo 
AND b.bookNo = c.bookNo 
AND b.bookName = '政治经济学'
AND a.readerNo IN (	SELECT a.readerNo 
	FROM Borrow a,Book b 
	WHERE a.bookNo = b.bookNo 
	AND b.bookName = '数据库系统概念')
UNION 
SELECT a.readerNo,a.readerName,b.bookName,c.borrowDate,c.returnDate 
FROM Reader a,Book b,Borrow c 
WHERE a.readerNo = c.readerNo 
AND b.bookNo = c.bookNo 
AND b.bookName = '数据库系统概念'
AND a.readerNo IN (	SELECT a.readerNo 
	FROM Borrow a,Book b 
	WHERE a.bookNo = b.bookNo 
	AND b.bookName = '政治经济学')

5、查询至少借阅过读者张小娟所借阅过的所有图书的读者编号、读者姓名和工作单位。

SELECT b.readerNo,b.readerName,b.workUnit 
FROM Borrow a ,Reader b 
WHERE bookNo IN (
		SELECT bookNo 
		FROM Borrow 
		WHERE readerNo =(
				SELECT readerNo 
				FROM Reader
				WHERE readerName = '张小娟')
	)
GROUP BY b.readerNo,b.readerName,b.workUnit;

6、查询至少有3本在借图书的读者编号、读者姓名以及在借图书的图书编号、图书名称，按读者编号升序、借阅日期降序排序输出。

SELECT a.readerNo,a.readerName,c.bookNo,c.bookName
FROM Reader a, Borrow b ,Book c 
WHERE a.readerNo = b.readerNo
AND b.returnDate IS NULL
GROUP BY a.readerNo,a.readerName,c.bookNo,c.bookName
HAVING count(b.bookNo)>2
ORDER BY a.readerNo 

未解决按照借阅日期降序

7、查找价格高于数据库系统概念的图书清单。（使用连接查询和子查询两种写法，体会写法的不同之处）

 #第一种方法，使用连接查询
SELECT a.bookNo,a.classNo,a.bookName,a.authorName,a.price,a.publishingDate,a.shopDate,a.shopNum  FROM Book AS a 
INNER JOIN Book AS b 
ON b.bookName = '数据库系统概念'
WHERE b.price < a.price
 #第二种方法，使用子查询
SELECT * FROM Book
WHERE price > (SELECT price FROM Book where bookName = '数据库系统概念')

8、查询从来没有接过书的单位名称。（分别使用 not in、not exists和连接查询三种写法实现，体会写法的不同之处）

#第一种方法，使用 not in
SELECT a.workUnit FROM Reader a,Borrow b 
WHERE a.readerNo not in (b.readerNo)
GROUP BY a.workUnit;
#第二种方法，使用 not exists
SELECT workUnit FROM Reader 
WHERE NOT EXISTS(
	SELECT readerNo FROM Borrow 
	WHERE Reader.readerNo = Borrow.readerNo);

#第三种方法，使用 连接查询
SELECT workUnit 
FROM Reader a LEFT OUTER JOIN  Borrow b 
ON a.readerNo = b.readerNo 
GROUP BY a.workUnit;

9、查询借的最多的图书的图书编号和图书名称。

SELECT b.bookNo,b.bookName 
FROM Book b 
WHERE b.bookNo = (
			SELECT bookNo FROM Borrow 
			GROUP BY bookNo HAVING COUNT(bookNo)=(
						SELECT  TOP 1 count(bookNo) FROM Borrow 
						GROUP BY bookNo 
						ORDER BY count(bookNo) DESC) 
			)

10、再图书表中计算每本书的价格与平均价格之差，结果显示图书编号、图书名称和平均差（价格-avg(价格)），并按照平均差降序排列。

SELECT bookNo,bookName,price-(SELECT top 1 (SELECT SUM(price) FROM Book)/(SELECT count(price) FROM Book )  as subs FROM Book ) AS avgSub 
FROM Book 
GROUP BY bookNo,bookName,price 
ORDER BY  avgSub ASC

11、根据图书表查找每类图书的价格最高的图书，显示分类号、图书名称和价格，并按照分类号升序。（分别使用exists、not exists和派生表查询三种写法实现，体会写法的不同之处）

#1. 使用exists查询
SELECT a.classNo,a.bookName,a.price 
FROM Book a
WHERE EXISTS (
  SELECT 1
	FROM Book b 
	WHERE a.classNo = b.classNo 
	GROUP BY b.classNo
	HAVING MAX(b.price) = a.price
  )
GROUP BY classNo,bookName,price 
ORDER BY a.classNo;

#2. 使用not exists查询
SELECT a.classNo,a.bookName,a.price 
FROM Book a
WHERE NOT EXISTS (
  SELECT 1
	FROM Book b 
	WHERE a.classNo = b.classNo 
	AND b.price > a.price 
  )
GROUP BY classNo,bookName,price 
ORDER BY a.classNo ASC;
#3. 使用派生表查询
SELECT classNo,bookName,price   
FROM Book where price IN(
SELECT MAX(price) as maxPrice FROM Book GROUP BY classNo)
ORDER BY classNo ASC;

12、查询所有借的图书的图书编号、图书名称、借阅次数以及根据借阅次数的排名（借阅次数越大，排名就靠前，类似根据成绩排名），并按照借阅次数降序排列。
使用两种方法：方法1：使用DENSE_RANK()over(order by ）或者RANK()over(order by）（延申掌握ROW_NUMBER()，NTILE(数字)over(order by ），row_number，
方法2：使用借阅次数的比较法，提示：先求出图书编号、图书名称、借阅次数，再根据借阅次数做比较，如果想得到的排名为1、2、2、4就不添加去重，如果得到的排名为：1、2、2、3则需要添加distinct。

13、查询借阅量排名第三的图书名称和图书编号。

SELECT a.bookName,a.bookNo 
FROM Book a INNER JOIN (
		SELECT TOP 3 bookNo,COUNT(*) AS borrow_count 
		FROM Borrow 
		GROUP BY bookNo
		ORDER BY borrow_count DESC
		) AS b 
ON a.bookNo = b.bookNo;