合肥网站设计哪家公司好,域名的格式是什么,网站建设前期情况说明,手机网游传奇关键字 row_number() over (partition by)
例如#xff0c;下面的数据#xff0c; 这是按照name分组后#xff0c;展示property值。 我们想得到这样的值; 第一步#xff1a;将每一组的property标上序号
select name,property,row_number() over (partition by name order…
关键字 row_number() over (partition by)
例如下面的数据 这是按照name分组后展示property值。 我们想得到这样的值; 第一步将每一组的property标上序号
select name,property,row_number() over (partition by name order by property desc) column_num from test
得到的结果: partition by 后面接的就应该是将要分组的字段。如果partition by property 相当于按照property分组那么后面的列序号column_num就是1111111 select b.MineID,b.MineName,b.economy,b.address,MAX(CASE WHEN num 1 THEN name END) AS value1,MAX(CASE WHEN num 1 THEN jobType END) AS jobType1,MAX(CASE WHEN num 1 THEN mobile END) AS phone1,MAX(CASE WHEN num 2 THEN name END) AS value2,MAX(CASE WHEN num 2 THEN jobType END) AS jobType2,MAX(CASE WHEN num 2 THEN mobile END) AS phone2,MAX(CASE WHEN num 3 THEN name END) AS value3,MAX(CASE WHEN num 3 THEN jobType END) AS jobType3,MAX(CASE WHEN num 3 THEN mobile END) AS phone3,MAX(CASE WHEN num 4 THEN name END) AS value4,MAX(CASE WHEN num 4 THEN jobType END) AS jobType4,MAX(CASE WHEN num 4 THEN mobile END) AS phone4,MAX(CASE WHEN num 5 THEN name END) AS value5,MAX(CASE WHEN num 5 THEN jobType END) AS jobType5,MAX(CASE WHEN num 5 THEN mobile END) AS phone5,MAX(CASE WHEN num 6 THEN name END) AS value6,MAX(CASE WHEN num 6 THEN jobType END) AS jobType6,MAX(CASE WHEN num 6 THEN mobile END) AS phone6,MAX(CASE WHEN num 7 THEN name END) AS value7,MAX(CASE WHEN num 7 THEN jobType END) AS jobType7,MAX(CASE WHEN num 7 THEN mobile END) AS phone7,MAX(CASE WHEN num 8 THEN name END) AS value8,MAX(CASE WHEN num 8 THEN jobType END) AS jobType8,MAX(CASE WHEN num 8 THEN mobile END) AS phone8,MAX(CASE WHEN num 9 THEN name END) AS value9,MAX(CASE WHEN num 9 THEN jobType END) AS jobType9,MAX(CASE WHEN num 9 THEN mobile END) AS phone9,MAX(CASE WHEN num 10 THEN name END) AS value10,MAX(CASE WHEN num 10 THEN jobType END) AS jobType10,MAX(CASE WHEN num 10 THEN mobile END) AS phone10from (select a.*,row_number() over (partition by a.MineName order by a.job_id asc) num from (selectm.MineID,m.MineName,dic.LABEL economy,t.ADDRESS address,a.job_id, person_name name,mobile,if (a.job_id is null,a.job,b.dic_name) jobTypefrom m_mine_keeper_info aLEFT JOIN k_dictionary b on b.dic_codea.job_id and b.dic_typeJobType left join k_mine m on m.MineIDa.MineIdleft join t_aj1_mj_jcxx_baseinfo t on t.CORP_IDm.MineID and t.DEL_FLAG0left join jcxx_sys_dict dic on dic.VALUEt.ECONOMY_TYPE and dic.TYPEbase_economy_typewhere ( a.is_deleted0 or a.is_deleted is null ) and a.job_id is not null ) a) b group by MineName
