mysql中函数row_number()如何使用它为结果集中的每一行生成序列号

因项目需要，行数据转列数据，并且要对同类数据取最后一条，如果是写java代码就不用说了，查询再过滤，但是会对分页啥啥的有影响，鄙人不太爱写那种一大堆java代码来处理一个简单问题。
首先我们要先把数据查询出来看看，这是按照日期、人、类型进行分组排序

SELECT task.*,
row_number() over (partition by task.person_zid,task.type,task.report_date order by task.create_time DESC) as group_idx ,person.zid as personZid,person.person_name as personName,IFNULL(TIMESTAMPDIFF(year,person.birth_date,NOW()),person.age) age,person.gender,person.hs_id as hisId,task.org_zid as orgZid,report_date reportDate,org.org_name orgName,org.org_code orgCode
FROM HS_REPORT_TASK task
LEFT OUTER JOIN VIEW_USER_AUTH_ORG vuao on task.org_zid = vuao.org_zid
LEFT OUTER JOIN HS_ORG org on org.zid = task.org_zid
LEFT OUTER JOIN HS_PERSON person on task.person_zid = person.zid 
LEFT OUTER JOIN HS_INPATIENT hi on task.person_zid = hi.person_zid
WHERE (task.type in ("smwt","cat","sgrq","sas","sds","psqi","mna","adl","fim","gad","phq") AND task.status not in ('RETRYED','INVALID')  AND task.org_zid = 'vnyksq5aBw') and task.person_zid = '930f8eab7d2c4c11aacac4987e5c2c80'

然后还要把这行数据转列数据，而且是动态的字段，当然经过处理显示为指定字段，获取排序规则为1的数据

这个是自动产生过后的语句有点多，不太好看。

SELECT personZid,personName,age,gender,hisId,orgZid,reportDate,orgName,orgCode,max(case task.`type` WHEN 'smwt' THEN task.zid  ELSE NULL end) smwt,max(case task.`type` WHEN 'smwt' THEN task.status  ELSE NULL end) smwtStatus,max(case task.`type` WHEN 'smwt' THEN task.status_desc  ELSE NULL end) smwtDesc,max(case task.`type` WHEN 'smwt' THEN task.version  ELSE NULL end) smwtVersion,max(case task.`type` WHEN 'cat' THEN task.zid  ELSE NULL end) cat,max(case task.`type` WHEN 'cat' THEN task.status  ELSE NULL end) catStatus,max(case task.`type` WHEN 'cat' THEN task.status_desc  ELSE NULL end) catDesc,max(case task.`type` WHEN 'cat' THEN task.version  ELSE NULL end) catVersion,max(case task.`type` WHEN 'sgrq' THEN task.zid  ELSE NULL end) sgrq,max(case task.`type` WHEN 'sgrq' THEN task.status  ELSE NULL end) sgrqStatus,max(case task.`type` WHEN 'sgrq' THEN task.status_desc  ELSE NULL end) sgrqDesc,max(case task.`type` WHEN 'sgrq' THEN task.version  ELSE NULL end) sgrqVersion,max(case task.`type` WHEN 'sas' THEN task.zid  ELSE NULL end) sas,max(case task.`type` WHEN 'sas' THEN task.status  ELSE NULL end) sasStatus,max(case task.`type` WHEN 'sas' THEN task.status_desc  ELSE NULL end) sasDesc,max(case task.`type` WHEN 'sas' THEN task.version  ELSE NULL end) sasVersion,max(case task.`type` WHEN 'sds' THEN task.zid  ELSE NULL end) sds,max(case task.`type` WHEN 'sds' THEN task.status  ELSE NULL end) sdsStatus,max(case task.`type` WHEN 'sds' THEN task.status_desc  ELSE NULL end) sdsDesc,max(case task.`type` WHEN 'sds' THEN task.version  ELSE NULL end) sdsVersion,max(case task.`type` WHEN 'psqi' THEN task.zid  ELSE NULL end) psqi,max(case task.`type` WHEN 'psqi' THEN task.status  ELSE NULL end) psqiStatus,max(case task.`type` WHEN 'psqi' THEN task.status_desc  ELSE NULL end) psqiDesc,max(case task.`type` WHEN 'psqi' THEN task.version  ELSE NULL end) psqiVersion,max(case task.`type` WHEN 'mna' THEN task.zid  ELSE NULL end) mna,max(case task.`type` WHEN 'mna' THEN task.status  ELSE NULL end) mnaStatus,max(case task.`type` WHEN 'mna' THEN task.status_desc  ELSE NULL end) mnaDesc,max(case task.`type` WHEN 'mna' THEN task.version  ELSE NULL end) mnaVersion,max(case task.`type` WHEN 'adl' THEN task.zid  ELSE NULL end) adl,max(case task.`type` WHEN 'adl' THEN task.status  ELSE NULL end) adlStatus,max(case task.`type` WHEN 'adl' THEN task.status_desc  ELSE NULL end) adlDesc,max(case task.`type` WHEN 'adl' THEN task.version  ELSE NULL end) adlVersion,max(case task.`type` WHEN 'fim' THEN task.zid  ELSE NULL end) fim,max(case task.`type` WHEN 'fim' THEN task.status  ELSE NULL end) fimStatus,max(case task.`type` WHEN 'fim' THEN task.status_desc  ELSE NULL end) fimDesc,max(case task.`type` WHEN 'fim' THEN task.version  ELSE NULL end) fimVersion,max(case task.`type` WHEN 'gad' THEN task.zid  ELSE NULL end) gad,max(case task.`type` WHEN 'gad' THEN task.status  ELSE NULL end) gadStatus,max(case task.`type` WHEN 'gad' THEN task.status_desc  ELSE NULL end) gadDesc,max(case task.`type` WHEN 'gad' THEN task.version  ELSE NULL end) gadVersion,max(case task.`type` WHEN 'phq' THEN task.zid  ELSE NULL end) phq,max(case task.`type` WHEN 'phq' THEN task.status  ELSE NULL end) phqStatus,max(case task.`type` WHEN 'phq' THEN task.status_desc  ELSE NULL end) phqDesc,max(case task.`type` WHEN 'phq' THEN task.version  ELSE NULL end) phqVersion
FROM (select s.* from ( SELECT task.*,person.zid as personZid,person.person_name as personName,IFNULL(TIMESTAMPDIFF(year,person.birth_date,NOW()),person.age) age,person.gender,person.hs_id as hisId,task.org_zid as orgZid,report_date reportDate,org.org_name orgName,org.org_code orgCode,
row_number() over (partition by task.person_zid,task.type,task.report_date order by task.create_time DESC) as group_idx 
FROM HS_REPORT_TASK task
LEFT OUTER JOIN VIEW_USER_AUTH_ORG vuao on task.org_zid = vuao.org_zid
LEFT OUTER JOIN HS_ORG org on org.zid = task.org_zid
LEFT OUTER JOIN HS_PERSON person on task.person_zid = person.zid 
LEFT OUTER JOIN HS_INPATIENT hi on task.person_zid = hi.person_zid
WHERE (task.type in ("smwt","cat","sgrq","sas","sds","psqi","mna","adl","fim","gad","phq") AND task.status not in ('RETRYED','INVALID')  AND task.org_zid = 'vnyksq5aBw' and task.person_zid = '930f8eab7d2c4c11aacac4987e5c2c80'))s  where s.group_idx = 1)task
GROUP BY personZid,reportDate
ORDER BY reportDate DESC

关心最重要的一句就行

row_number() over (partition by task.person_zid,task.type,task.report_date order by task.create_time DESC) as group_idx 

哦了，记录下，很久之前写的因为一个bug调试，发现忘记了，记录下