34,571
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#Book') is null
drop table #Book
Go
Create table #Book([BID] int,[title] nvarchar(23),[author] nvarchar(22))
Insert #Book
select 1,N'书名1',N'张三' union all
select 2,N'书名2',N'李四'
GO
if not object_id(N'Tempdb..#Borrow') is null
drop table #Borrow
Go
Create table #Borrow([BorrowID] int,[Bid] int)
Insert #Borrow
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,2 union all
select 6,1
Go
--测试数据结束
--1、
SELECT title ,
author
FROM #Book
JOIN ( SELECT TOP 1
bid ,
COUNT(1) AS count
FROM #Borrow
GROUP BY Bid
ORDER BY COUNT(1) DESC
) t ON t.bid = #book.BID
--2、
SELECT author ,
t.count AS 借阅总次数
FROM #Book
JOIN ( SELECT TOP 1
bid ,
COUNT(1) AS count
FROM #Borrow
GROUP BY Bid
ORDER BY COUNT(1) DESC
) t ON t.bid = #book.BID
--取一条记录
SELECT TOP 1 b.Title,b.author,COUNT(*) AS 次数
FROM borrow AS a
INNER JOIN book AS b ON b.BID=a.BID
GROUP BY b.BID,b.Title,b.author
ORDER BY COUNT(*) DESC
--支持并列第一
SELECT Title ,
author
FROM ( SELECT Title ,
author ,
DENSE_RANK() OVER ( ORDER BY t1.次数 DESC) AS RN
FROM ( SELECT b.BID ,
b.Title ,
b.author ,
COUNT(*) AS 次数
FROM borrow AS a
INNER JOIN book AS b ON b.BID = a.BID
GROUP BY b.BID ,
b.Title ,
b.author
) AS T1
) AS TT1
WHERE RN = 1;
--作者支持并列第一
SELECT author ,
次数
FROM ( SELECT author ,
次数 ,
DENSE_RANK() OVER ( ORDER BY t1.次数 DESC ) AS RN
FROM ( SELECT b.author ,
COUNT(*) AS 次数
FROM borrow AS a
INNER JOIN book AS b ON b.BID = a.BID
GROUP BY b.author
) AS T1
) AS TT1
WHERE RN = 1;