看了网友的sql分析,自己写一个:
oracle:分组后排序
SELECT NAME,score, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_r FROM students;
mysql:分组后排序
SELECT NAME,@rk:=IF(@score=score,@rk,@rk+1) dense_r,@score:=score score FROM (SELECT NAME,score FROM students ORDER BY score DESC)a, (SELECT @rk:=0,@score:=NULL)b;
写了一个我自己的sql语句:
-- 1:这个是关联查询出合同和租金规则的
select hlcRentRuleStartDate, hlcRentRuleEndDate, @rk:=IF(@HlcBarCode=HlcBarCode,@rk,@rk+1),@HlcBarCode:=HlcBarCode HlcBarCode
from (
select t1.hlcRentRuleStartDate, t1.hlcRentRuleEndDate, t1.HlcRentRulePeriod, t2.HlcBarCode, t2.HlcCode from tHLCRentRlue t1 left join tHouseLeaseContract t2
on t1.hlcRentRuleContractCode=t2.HlcCode where t2.insertTime >= '2022-01-01' and t2.insertTime <= '2023-02-01' and t1.HlcRentRulePeriod='PayCyc_006' and t1.HlcRentRulePeriod='PayCyc_006'
) a, (SELECT @rk:=0,@HlcBarCode:=NULL)b;
希望此文帮助到你!