POSTGRESQL-分类计算序号

已知一个分类字段，需要按分类字段计算一个ID值，每个分类都从1开始计数，

并且流水号前用0填充成6位数

最终形成   分类字段+6位流水号

JDCODE ：是分类的字段，同时也是要拼接的字段

SELECT  JDCODE  ,'分类' || JDCODE || '_流水号_' || LPAD(ROW_NUMBER() OVER (PARTITION BY JDCODE ORDER BY JDCODE)::text, 6, '0') AS 新字段值

, JDCODE  || LPAD(ROW_NUMBER() OVER (PARTITION BY JDCODE ORDER BY JDCODE)::text, 6, '0') AS 新字段值

FROM public."WHHP_BLD_POI";

posgresql中不允许在update语句中直接使用 row_number()

update public."WHHP_BLD_POI"

set poiid =JDCODE  || LPAD(ROW_NUMBER() OVER (PARTITION BY JDCODE ORDER BY JDCODE)::text, 6, '0')

会提示错误

需要嵌套成子查询，并保证对应

UPDATE your_table

SET new_field = subquery.new_valueFROM (

  SELECT id, '分类' || category || '_流水号_' || LPAD(ROW_NUMBER() OVER (PARTITION BY category ORDER BY category)::text, 6, '0') AS new_value

  FROM your_table

) AS subqueryWHERE your_table.id = subquery.id;

UPDATE public."WHHP_BLD_POI"

SET poiid = subquery.new_value

FROM (

  SELECT id, JDCODE  || LPAD(ROW_NUMBER() OVER (PARTITION BY JDCODE ORDER BY JDCODE)::text, 6, '0') AS new_value

  FROM public."WHHP_BLD_POI"

) AS subquery

WHERE public."WHHP_BLD_POI".id = subquery.id;