程式筆記

若同一table資料內比較, mssql可用lag或lead函式, 但如果來源資料不同?

測試資料建立

--依發票數量找出所屬級距
create table #Invoices (CompanyID varchar(10), Counts int);
insert into #Invoices (CompanyID, Counts) 
values ('21383',125);

--若發票數量1張-99張收250, 100-249張收300, 依此類推
create table #Grade (CompanyID varchar(10), GradeCount int, BasicFee int);
insert into #Grade (CompanyID, GradeCount, BasicFee) 
values ('21383',1,250),('21383',100,300),('21383',200,400);

SQL

select t.CompanyID,t.Counts,t.gradecount as topNum,b.GradeCount as buttomNum,BasicFee
from
(
   select 
		i.CompanyID
        ,Gradecount
		,ROW_NUMBER() OVER (PARTITION BY a.companyID ORDER BY gradecount ASC) as Id
		,BasicFee
		,i.Counts
    from #Invoices i inner join #Grade a
	on i.CompanyID=a.CompanyID
) t
LEFT JOIN (select 
	a.CompanyID,gradecount
	,ROW_NUMBER() OVER (PARTITION BY a.CompanyID ORDER BY Gradecount ASC) as Id
from #Grade a) AS B 
ON t.Id = B.Id - 1 and t.CompanyID=b.CompanyID
where t.Counts between t.Gradecount and iif(b.GradeCount is null,999999999,b.GradeCount)
ORDER BY t.CompanyID, t.Id

drop table #Invoices;
drop table #Grade;

//result
CompanyID	Counts	topNum	buttomNum	BasicFee
21383		125		100		250			300

IQueryable

//找出前一筆
        public static int FindPrevious(
            this EntitySet<Grade> list,
            int compareValue)
        {

            var grade = list.OrderBy(x => x.GradeCount)
                        .Where(elem => elem.GradeCount <= compareValue)
                       .LastOrDefault();//符合條件下(100級距)最後(大)的一筆

            return (grade == null) ? 0 : grade.BasicFee;
        }

Foreach

//找出前一筆
        public static int FindPrevious(int invoiceCounts) 
        {
            int basicFee = 0;

            Grade curr = null, prev = null;
            foreach (var grade in Grades)
            {
                    curr = grade;
                    if (curr.GradeCount > invoiceCounts)
                        break;//超過條件的第一筆(200級距), 往前取一筆(100級距)
                    else
                        prev = curr;
            }
            basicFee = (prev == null) ? 0 : prev.BasicFee;

            return basicFee;
        }

泛型(用System.Linq.Dynamic可達動態條件)

待研究.
參考:https://stackoverflow.com/questions/29765174/getting-next-and-previous-on-a-queryable