SELECT BName,Price
FROM [Library System].DBO.Book
WHERE Price in
( SELECT MAX(Price)
FROM [Library System].DBO.Book,[Library System].DBO.Borrow,[Library System].DBO.Reader
WHERE Reader.RID=Borrow.RID AND BOOK.BID=Borrow.BID
GROUP BY READER.RID
)
这是自己写的SQL语句,意图是想显示每个读者借阅的图书里最贵的那本的书名和价格。
子查询结果是
300
300
32
300
32
查询结果是
Chinese 32
English 300
把重复的数据都忽略了
FROM [Library System].DBO.Book,[Library System].DBO.Borrow,[Library System].DBO.Reader
where
Reader.RID=Borrow.RID AND BOOK.BID=Borrow.BID
group by Reader.'读者名称',BName
SELECT book.bname,MAX(Price)
FROM [Library System].DBO.Book,[Library System].DBO.Borrow,[Library System].DBO.Reader
WHERE Reader.RID=Borrow.RID AND BOOK.BID=Borrow.BID
GROUP BY READER.RID,book.bname