我只需要列出数据库中对公司的前五个请求。我有两个表,分别名为"请求"和"公司"。给定公司可能会收到任意数量的请求。但是,我只需要查询并列出数据库中所有公司的前 5 个请求。
select top 5 *
from Request as r
INNER JOIN Company c
ON r.Company_id=c.Company_ID
我已经完成了上述查询,但它只返回表中的前五个,而不是所有成员的前五个。如何更正我的查询?
With r
as
(select *, row_number() over(partition by Company_id order by RequestDate) as rown
from Request as)
Select *
from r join Company c on c.Company_id=r.Company_id
Where rown<=5
我认为您正在寻找公司表中存在的每个公司的前 5 个请求。在这种情况下,您可以在子选择中按公司rank()
每个请求,然后选择排名<= 5
的位置:
select company_id, request_id
from
( select c.company_id, r.request_id, rank() over (partition by c.company_id order by request_id) as Rank
from request r
inner join company c on
r.company_id = c.company_id
) ranked
where rank <= 5
您需要按确定"第一性"的字段进行排序。 可能是这样的:
按 r.请求日期订购
;WITH companyrecords
AS (SELECT Company.*,
Row_number() OVER (partition BY r.Company_id ORDER BY
r.Company_id
ASC)
AS
RowNumber
FROM Request AS r
INNER JOIN Company c
ON r.Company_id = c.Company_ID)
SELECT *
FROM companyrecords
WHERE RowNumber <= 5