-- 内层提供所有卖过的BookId,外层取这些编号对应的图书SELECT BookId,Title FROM dbo.BookWHERE BookId IN (SELECT BookId FROM dbo.Sale)ORDER BY BookId;-- SQL入门、Python基础、唐诗选读;SQL入门卖两次也只显示一行
-- 每本图书都检查“没有自己的销售记录”SELECT b.Title FROM dbo.Book bWHERE NOT EXISTS(SELECT 1 FROM dbo.Sale s WHERE s.BookId=b.BookId);-- 数据库原理、散文精选、星空漫游
NOT IN也能表示不属于集合,但如果其子查询结果含NULL,可能整批比较变UNKNOWN。课程Sale.BookId非空,当前例子安全;遇到可空来源更推荐NOT EXISTS表达“没有关联”。
5 和本分类平均价比较
-- 内层根据外层当前图书的CategoryId,计算该分类的均价SELECT b.Title,b.Price FROM dbo.Book bWHERE b.Price >( SELECT AVG(x.Price) FROM dbo.Book x WHERE x.CategoryId=b.CategoryId);-- 数据库原理79高于计算机均价69;散文精选45高于文学均价40
SELECT Title FROM dbo.BookWHERE CategoryId IN(SELECT CategoryId FROM dbo.Category WHERE CategoryName=N'文学');SELECT b.Title FROM dbo.Book bWHERE EXISTS(SELECT 1 FROM dbo.Category c WHERE c.CategoryId=b.CategoryId AND c.CategoryName=N'文学');SELECT c.CategoryName FROM dbo.Category cWHERE NOT EXISTS(SELECT 1 FROM dbo.Book b WHERE b.CategoryId=c.CategoryId); -- 科普