窗口函数row_number() over (partition by 字段1 order by 字段2)

数据库中的窗口函数

1.介绍

SQL Server、MySQL还是Oracle都有窗口函数。今天前辈教我这个东西，还是第一次见识这个函数，还是有收获的。

2.开窗函数的语法

窗口函数的语法：over (partition by 字段1 order by 字段2)，括号中的两个关键词partition by 和order by 可以只出现一个。over() 前面是一个函数，如果是聚合函数，那么order by 不能一起使用。

3. 排名开窗函数row_number() over (partition by 字段1 order by 字段2)

今天在工作中遇到一个问题，由于一个保单号（主键）下面有多个产品，每个产品下能操作的保全项也不一样。导致列表显示一个保单号前面所有字段数值都一样，就是最后一个字段不一样。

例如图中标记的两套记录，我想剔除下面的一条。

select a.grpcontno,
	   a.appntno,
	   a.grpname,
	   a.state,
	   a.peoples,
	   ....
	   a.edortype
from (select b.grpcontno,
			 b.appntno,
			 b.grpname,
			 b.state,
			 b.peoples,
			 ......
			 b.edortype,
			 row_number() over (partition by grpcontno order by len desc) as t 
	 from (select distinct a.grpcontno,
	 					   a.appntno,
	 					   a.state,
	 					   (select count(1) 
	 					   		from lcinsured lc 
	 					   	where lc.grpcontno = a.grpcontno
	 					   		and lc.insuredstat = '11') peoples,
	 					   )
	 					   ......
	 					   (select to_char(wm_concat(lm.edorcode))
	 					   		from lmriskedoritem lm
	 					   	where lm.edorcode in 
	 					   		  ('NI', 'ZT', 'IR', 'PB', 'IC')
	 					   and lm.riskcode = lm.riskcod) edortype,
	 					   (select length(to_char(wm_concat(lm.edorcode))
	 					   		from lmriskedoritem lm
	 					   	where lm.edorcode in 
	 					   		  ('NI', 'ZT', 'IR', 'PB', 'IC')
	 					   and lm.riskcode = lm.riskcod)) len
	 	from lcgrpcont     a,
	 		 ldpbalanceon  b,
	 		 lccontgetpol  c,
	 		 lcproposal    d,
	 		 lcagenttocont x
	 	left join lcpol lc
	 	  on x.policyno = lc.grpcontno
	 	where a.grpcontno = b.grpcontno
	 	and a.prtno = d.grppropno
	 	and d.contplantype = '00'
	 	and a.grpcontno = c.contno
	 	and x.policyno = a.grpcontno
	 	and c.receivepoldate is not null
	 	and lc.riskcode <> '8904'
	 	and a.state = '11'
	 	order by c.cvalidate desc) b ) a
where a.t = 1

这样的话就能剔除掉上面所说的那条数据。
该例子中，子查询有点多，看的眼花。。。。

学习最重要的就是会举一反三，下面列举一个简单的例子。
查询每个学生成绩最高的一次记录。

-- 方法一： 开窗函数
SELECT
	b.sid,
	b.name,
	b.gender,
	b.age,
	b.score 
FROM
	(
	SELECT
		a.sid,
		a.name,
		a.gender,
		a.age,
		a.score,
		ROW_NUMBER() over ( PARTITION BY a.sid ORDER BY a.score DESC ) AS t 
	FROM
		(
		SELECT
			t1.sid,
			t1.NAME,
			( SELECT ld.codename FROM ldcode ld WHERE ld.codetype = 'gender' AND ld.`code` = t1.gender ) gender,
			t1.age,
			t2.score 
		FROM
			stu_info t1
			LEFT JOIN c_course t2 ON t1.sid = t2.sid 
		-- ORDER BY t2.score DESC 
		) a 
	) b 
WHERE
	b.t = 1 ;

-- 方法二： group by 
SELECT
		t1.sid,
		t1.name,
		( SELECT ld.codename FROM ldcode ld WHERE ld.codetype = 'gender' AND ld.`code` = t1.gender ) gender,
		t1.age age,
		MAX( t2.score) score 
	FROM
		stu_info t1
		LEFT JOIN c_course t2 ON t1.sid = t2.sid 
	GROUP BY
		t1.sid, t1.age
		ORDER BY MAX( t2.score) 

4.总结

遇到困难，首先自己要先去思考，自己动手实践。实在是搞不定就请教大佬，不要不敢问，脸皮厚一点。没人会觉得你差，学会积累。拜拜啦，洗澡睡觉。