分組排序取最大sql理解

--用戶過濾（只能看到當前用戶對應部門用戶權限表中的部門）
select h.pk_tbdept 
from jygyl_bmyhqxb h 
left join jygyl_bmyhqxb_b b 
on h.pk_bmyhqx = b.pk_bmyhqx 
where isnull(h.dr,0) = 0 
and isnull(b.dr,0) = 0 
and b.pk_user = '用戶主鍵'--查詢項目產值及回款填報各部門對應每個項目的最大月份的數據的合計
SELECT pk_dept as pk_dept,sum(nsrzbwcqk) as nsrzbwcqk
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY xmczjhktb.project_code ORDER BY xmczjhktb.yearmth desc) as rank,xmczjhktb.*FROM (SELECT mon.yearmth,pro.project_code,dept.pk_dept,(select sum(yzbhsdnljwcnum) from jygyl_xmczjhktb_b b where b.pk_xmczjhktb = h.pk_xmczjhktb and isnull(h.dr,0) = 0 and isnull(b.dr,0) = 0 and csrcid IS NOT NULL AND LEN(csrcid) > 0) as nsrzbwcqkFROM jygyl_xmczjhktb hINNER JOIN bd_accperiodmonth mon ON h.tbyf = mon.pk_accperiodmonthINNER JOIN bd_project pro ON h.xmbh = pro.pk_projectINNER JOIN org_dept dept ON dept.pk_dept = pro.def2WHERE isnull(h.dr,0) = 0AND isnull(mon.dr,0) = 0AND isnull(pro.dr,0) = 0AND isnull(dept.dr,0) = 0AND h.vbillstatus = 1AND	mon.yearmth <='2024-05') xmczjhktb) h 
WHERE rank = 1
GROUP BY pk_dept--查詢項目產值及回款填報各部門對應每個項目的最大月份的主表主鍵
SELECT pk_xmczjhktb
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY xmczjhktb.project_code ORDER BY xmczjhktb.yearmth DESC) AS rank,xmczjhktb.*FROM(SELECT mon.yearmth,pro.project_code,dept.pk_dept,h.pk_xmczjhktbFROM jygyl_xmczjhktb hINNER JOIN bd_accperiodmonth mon ON h.tbyf = mon.pk_accperiodmonthINNER JOIN bd_project pro ON h.xmbh = pro.pk_projectINNER JOIN org_dept dept ON dept.pk_dept = pro.def2WHERE isnull(h.dr, 0) = 0AND isnull(mon.dr, 0) = 0AND isnull(pro.dr, 0) = 0AND isnull(dept.dr, 0) = 0AND h.vbillstatus = 1AND mon.yearmth <= '2024-05'  AND dept.pk_dept = '1001A1100000000000TG') xmczjhktb) h
WHERE rank = 1--查詢部門新簽合同維護各部門查詢年份的最大月份數據
SELECT yearmth,tbdept,nxqzbwcqk
FROM(SELECT mon.yearmth,h.tbdept,(SELECT SUM (b.je) FROM jygyl_bmxqhtwh_b b WHERE b.pk_bmxqhtwh = h.pk_bmxqhtwh AND isnull(b.dr, 0) = 0) AS nxqzbwcqk,ROW_NUMBER () OVER (PARTITION BY h.tbdept ORDER BY mon.yearmth DESC) AS rownoFROM jygyl_bmxqhtwh hINNER JOIN bd_accperiodmonth mon ON h.tbmonth = mon.pk_accperiodmonthWHERE isnull(h.dr, 0) = 0AND h.vbillstatus = 1AND mon.yearmth >= '2024-01'AND mon.yearmth <= '2024-05') h
WHERE rowno = 1--查詢部門新簽合同維護該部門查詢年份的最大月份的主表主鍵
SELECT pk_bmxqhtwh
FROM(SELECT mon.yearmth,h.tbdept,h.pk_bmxqhtwh,ROW_NUMBER () OVER (PARTITION BY h.tbdept ORDER BY mon.yearmth DESC) AS rownoFROM jygyl_bmxqhtwh hINNER JOIN bd_accperiodmonth mon ON h.tbmonth = mon.pk_accperiodmonthWHERE isnull(h.dr, 0) = 0AND h.vbillstatus = 1AND mon.yearmth >= '2024-01'AND mon.yearmth <= '2024-05') h
WHERE rowno = 1
AND tbdept = '1001A21000000008128D'--查詢部門新簽合同維護該部門查詢年份的最大月份的主表主鍵
SELECT TOP 1 h.pk_bmxqhtwh
FROM jygyl_bmxqhtwh h
INNER JOIN bd_accperiodmonth mon ON h.tbmonth = mon.pk_accperiodmonth
WHERE isnull(h.dr, 0) = 0
AND isnull(mon.dr, 0) = 0
AND h.vbillstatus = 1
AND mon.yearmth >= '2024-01'
AND mon.yearmth <= '2024-05'
and tbdept = '1001A21000000008128D'
ORDER BY mon.yearmth DESC