分组排序取最大sql理解

分组排序取最大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 nsrzbwcqk
			FROM jygyl_xmczjhktb h
			INNER JOIN bd_accperiodmonth mon ON h.tbyf = mon.pk_accperiodmonth
			INNER JOIN bd_project pro ON h.xmbh = pro.pk_project
			INNER JOIN org_dept dept ON dept.pk_dept = pro.def2
			WHERE isnull(h.dr,0) = 0
			AND isnull(mon.dr,0) = 0
			AND isnull(pro.dr,0) = 0
			AND isnull(dept.dr,0) = 0
			AND h.vbillstatus = 1
			AND	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_xmczjhktb
		   FROM jygyl_xmczjhktb h
		   INNER JOIN bd_accperiodmonth mon ON h.tbyf = mon.pk_accperiodmonth
           INNER JOIN bd_project pro ON h.xmbh = pro.pk_project
           INNER JOIN org_dept dept ON dept.pk_dept = pro.def2
           WHERE isnull(h.dr, 0) = 0
           AND isnull(mon.dr, 0) = 0
           AND isnull(pro.dr, 0) = 0
           AND isnull(dept.dr, 0) = 0
           AND h.vbillstatus = 1
           AND 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 rowno
		 FROM jygyl_bmxqhtwh h
		 INNER JOIN bd_accperiodmonth mon ON h.tbmonth = mon.pk_accperiodmonth
		 WHERE isnull(h.dr, 0) = 0
		 AND h.vbillstatus = 1
		 AND 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 rowno
	 FROM jygyl_bmxqhtwh h
	 INNER JOIN bd_accperiodmonth mon ON h.tbmonth = mon.pk_accperiodmonth
	 WHERE isnull(h.dr, 0) = 0
	 AND h.vbillstatus = 1
	 AND 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


--查询部门收款明细维护各部门查询年份的最大月份最大版本号数据
SELECT yearmth,
	   tbbm,
	   nhkzbwcqk
FROM(SELECT acc.yearmth,
			h.tbbm,
			h.version,
			(SELECT SUM (b.klqzjdk) FROM jygyl_bmskmxwh_b b WHERE b.pk_bmskmxwh = h.pk_bmskmxwh AND isnull(b.dr, 0) = 0) AS nhkzbwcqk,
			ROW_NUMBER () OVER (PARTITION BY h.tbbm ORDER BY acc.yearmth DESC,h.version desc) AS rowno
		 FROM jygyl_bmskmxwh h
		 INNER JOIN bd_accperiodmonth acc ON h.tbyf = acc.pk_accperiodmonth
		 WHERE isnull(h.dr, 0) = 0
		 AND h.vbillstatus = 1
		 AND acc.yearmth >= '2024-01'
		 AND acc.yearmth <= '2024-05'
		) h
WHERE rowno = 1

--查询部门收款明细维护各部门查询年份的最大月份最大版本号数据
SELECT h.tbbm AS tbbm,
	   SUM (b.klqzjdk) AS nhkzbwcqk
FROM jygyl_bmskmxwh h
LEFT JOIN jygyl_bmskmxwh_b b ON b.pk_bmskmxwh = h.pk_bmskmxwh
LEFT JOIN bd_accperiodmonth acc ON acc.pk_accperiodmonth = h.tbyf
LEFT JOIN (SELECT tbbm,
				  tbyf,
				  MAX(version) as maxversion 
		   FROM jygyl_bmskmxwh 
		   WHERE isnull(dr, 0) = 0 
		   GROUP BY tbbm,tbyf) temp ON h.tbbm = temp.tbbm AND h.tbyf = temp.tbyf
WHERE isnull(h.dr, 0) = 0
AND isnull(b.dr, 0) = 0
AND isnull(acc.dr, 0) = 0
AND version = maxversion
AND h.vbillstatus = 1
AND acc.yearmth = (SELECT MAX (acc.yearmth) 
				   FROM jygyl_bmskmxwh h1 
				   LEFT JOIN bd_accperiodmonth acc ON acc.pk_accperiodmonth = h1.tbyf 
				   WHERE SUBSTRING (acc.yearmth, 1, 4) = '2024' 
				   AND acc.yearmth <= '2024-05' 
				   AND isnull(h1.dr, 0) = 0 
				   AND h1.vbillstatus = 1 
				   AND h1.tbbm = h.tbbm)
GROUP BY h.tbbm


--查询部门收款明细维护该部门查询年份的最大月份最大版本号的主表主键
SELECT pk_bmskmxwh
FROM(SELECT acc.yearmth,
			h.tbbm,
			h.pk_bmskmxwh,
			ROW_NUMBER () OVER (PARTITION BY h.tbbm ORDER BY acc.yearmth DESC,h.version desc) AS rowno
		 FROM jygyl_bmskmxwh h
		 INNER JOIN bd_accperiodmonth acc ON h.tbyf = acc.pk_accperiodmonth
		 WHERE isnull(h.dr, 0) = 0
		 AND h.vbillstatus = 1
		 AND acc.yearmth >= '2024-01'
		 AND acc.yearmth <= '2024-05'
		) h
WHERE rowno = 1
AND tbbm = '1001A1100000000000U8'


--查询部门收款明细维护该部门查询年份的最大月份最大版本号的主表主键
SELECT TOP 1 h.pk_bmskmxwh
FROM jygyl_bmskmxwh h
INNER JOIN bd_accperiodmonth acc ON h.tbyf = acc.pk_accperiodmonth
LEFT JOIN (SELECT tbbm, tbyf, MAX (version) maxversion FROM jygyl_bmskmxwh WHERE isnull(dr, 0) = 0 GROUP BY tbbm, tbyf) temp 
ON h.tbbm = temp.tbbm AND h.tbyf = temp.tbyf
WHERE isnull(h.dr, 0) = 0
and h.version = maxversion
AND isnull(acc.dr, 0) = 0
AND h.vbillstatus = 1
AND acc.yearmth >= '2024-01'
AND acc.yearmth <= '2024-05'
and h.tbbm = '1001A1100000000000U8'
ORDER BY acc.yearmth DESC