将SQL Rank()转换为LINQ或其他选项
本文关键字:LINQ 其他 选项 转换 SQL Rank | 更新日期: 2023-09-27 18:29:12
我有下面的SQL语句,它按预期工作。然而,我想把它翻译成LINQ语句(Lambda??),这样它就可以与我的DAL的其余部分相匹配。然而,我看不出如何在LINQ中模拟Rank()。
我把它发布在这里的原因可能是错误的,是想看看是否有人可以替代Rank()
语句,这样我就可以把它切换过来。或者,如果有一种在LINQ中表示Rank()
的方式,也将受到赞赏。
USE CMO
SELECT vp.[PersonID] AS [PersonId]
,ce.[EnrollmentID]
,vp.[FirstName]
,vp.[LastName]
,ce.[EnrollmentDate]
,ce.[DisenrollmentDate]
,wh.WorkerCategory
FROM [dbo].[vwPersonInfo] AS vp
INNER JOIN
(
[dbo].[tblCMOEnrollment] AS ce
LEFT OUTER JOIN
(
SELECT *
,RANK()OVER(PARTITION BY EnrollmentID ORDER BY CASE WHEN EndDate IS NULL THEN 1 ELSE 2 END, EndDate DESC, StartDate DESC) AS whrank
FROM [dbo].[tblWorkerHistory]
WHERE WorkerCategory = 2
) AS wh
ON ce.[EnrollmentID] = wh.[EnrollmentID] AND wh.whrank = 1
)
ON vp.[PersonID] = ce.[ClientID]
WHERE (vp.LastName NOT IN ('Client','Orientation','Real','Training','Matrix','Second','Not'))
AND (
(wh.[EndDate] <= GETDATE())
OR wh.WorkerCategory IS NULL
)
AND (
(ce.[DisenrollmentDate] IS NULL)
OR (ce.[DisenrollmentDate] >= GetDate())
)
这里有一个示例,展示了我如何在Linq:中模拟Rank()
var items = new[]
{
new { Name = "1", Value = 2 },
new { Name = "2", Value = 2 },
new { Name = "3", Value = 1 },
new { Name = "4", Value = 1 },
new { Name = "5", Value = 3 },
new { Name = "6", Value = 3 },
new { Name = "7", Value = 4 },
};
var q = from s in items
orderby s.Value descending
select new
{
Name = s.Name,
Value = s.Value,
Rank = (from o in items
where o.Value > s.Value
select o).Count() + 1
};
foreach(var item in q)
{
Console.WriteLine($"Name: {item.Name} Value: {item.Value} Rank: {item.Rank}");
}
输出
Name: 7 Value: 4 Rank: 1
Name: 5 Value: 3 Rank: 2
Name: 6 Value: 3 Rank: 2
Name: 1 Value: 2 Rank: 4
Name: 2 Value: 2 Rank: 4
Name: 3 Value: 1 Rank: 6
Name: 4 Value: 1 Rank: 6
LINQ内置了秩函数性,但不在查询语法中。当使用方法语法时,大多数linq函数有两个版本——普通版本和提供秩的版本。
一个简单的例子是,只选择其他每个学生,然后将结果序列中的索引添加到结果中:
var q = class.student.OrderBy(s => s.studentId).Where((s, i) => i % 2 == 0)
.Select((s,i) => new
{
Name = s.Name,
Rank = i
}
如果您想模拟排名,那么您可以使用以下linq查询。
var q = (from s in class.student
select new
{
Name = s.Name,
Rank = (from o in class.student
where o.Mark > s.Mark && o.studentId == s.studentId
select o.Mark).Distinct().Count() + 1
}).ToList();
您可以通过以下方式使用订单:
var q = (from s in class.student
orderby s.studentId
select new
{
Name = s.Name,
Rank = (from o in class.student
where o.Mark > s.Mark && o.studentId == s.studentId
select o.Mark).Distinct().Count() + 1
}).ToList();
但在此查询中,排序依据并不重要。
基于@Totero的答案,但有一个lamda实现。分数越高=级别越高。
var rankedData = data.Select(s => new{
Ranking = data.Count(x => x.Value > s.Value)+1,
Name = s.Key,
Score = s.Value});
对于此输入:
{ 100, 100, 98, 97, 97, 97, 91, 50 }
您将得到以下输出:
- 得分:排名
- 100:1
- 100:1
- 98:3
- 97:4
- 97:4
- 97:4
- 91:6
- 50:7