在 where 子句中使用 row_number() over(partition by col order by col)

我有這些表，我需要在其中使用這樣Row_number()的where子句

where spectrumcms.staffchilddiscountpolicy.childno =
(select  ROW_NUMBER() Over (Partition by std.GuardianCNIC  order by std.ID ASC))

我有這樣的桌子

現在我有這個查詢

select
 std.ID as ID,
 std.FirstName,
 std.LastName,
 emp.ID as EmpID,
 emp.FirstName + '' + emp.LastName as EmpName,
 emp.CNIC,
 (
   select count(*) as NoOfChilds
     from StudentManagement.SpectrumCMS.Student stdd
     join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
     where empp.ID = emp.ID and stdd.CompanyID = std.CompanyID and empp.CompanyID = emp.CompanyID
 ) as NoOfchilds,
 ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo 

from StudentManagement.SpectrumCMS.Student std
join HRManagement.SpectrumCMS.Employee emp on std.GuardianCNIC = emp.cnic
where std.CompanyID = 20145
 and emp.CompanyID = 20145
 and std.IsActive = 1
 and emp.IsActive = 1
order by emp.CNIC

現在來談談我為什麼需要的主要問題where=row_number()over(partition by col order by col)：

因為我需要從我發布圖片的表中獲取上述查詢的最後一列中的百分比。

這就是我目前得到的

現在在最後一percentage列，我需要percentage從staffchilddiscountpolicy我添加圖片的表格中

如評論中所述，您不能以該容量使用視窗功能。相反，您應該將第一個查詢放入子查詢或 CTE 中，這樣您就可以ChildNo在它和SpectrumCMS.StaffChildsDiscountPolicy表之間加入，如下所示：

WITH CTE_StudentChildNumbers AS
(
   select
       std.ID as ID,
       std.FirstName,
       std.LastName,
       emp.ID as EmpID,
       emp.FirstName + '' + emp.LastName as EmpName,
       emp.CNIC,
       (
           select count(*) as NoOfChilds
           from StudentManagement.SpectrumCMS.Student stdd
           join HRManagement.SpectrumCMS.Employee empp on stdd.GuardianCNIC = empp.CNIC
           where empp.ID = emp.ID 
               and stdd.CompanyID = std.CompanyID 
               and empp.CompanyID = emp.CompanyID
       ) as NoOfchilds,
      ROW_NUMBER() Over (Partition by std.GuardianCNIC order by std.ID ASC) as ChildNo 
  from StudentManagement.SpectrumCMS.Student std
  join HRManagement.SpectrumCMS.Employee emp on  std.GuardianCNIC = emp.cnic
  where std.CompanyID = 20145
      and emp.CompanyID = 20145
      and std.IsActive = 1
      and emp.IsActive = 1
)

SELECT 
   SCN.ID, 
   SCN.FirstName, 
   SCN.LastName, 
   SCN.EmpID, 
   SCN.EmpName, 
   SCN.CNIC, 
   SCN.NoOfchilds, 
   SCN.ChildNo,
   SCDP.Percentage
FROM CTE_StudentChildNumbers AS SCN
INNER JOIN SpectrumCMS.StaffChildsDiscountPolicy SCDP
   ON SCN.ChildNo = SCDP.ChildNo
WHERE SCDP.CompanyID = 20145 -- Not sure if you wanted the same filters from your screenshot, feel free to remove if not needed
   AND SCDP.IsActive = 1 
ORDER BY SCN.CNIC

引用自：https://dba.stackexchange.com/questions/295270