Dense_rank第一个Oracle到Postgresql的转换
Dense_rank第一个Oracle到Postgresql的转换
提问于 2018-02-06 16:49:35
我正在尝试将以下Oracle查询转换为Postgresql。我可以转换其余的块，问题是我不知道如何转换这个块：
SELECT ai.uid
,max(ai.OWNER) KEEP (
dense_rank first ORDER BY ai.AGENT_ID DESC
) AS OWNER
,max(ai.EMPLOYEE_KEY) KEEP (
dense_rank first ORDER BY ai.AGENT_ID DESC
) AS EMPLOYEE_KEY
,max(ai.MANAGER_LOGIN) KEEP (
dense_rank first ORDER BY ai.AGENT_ID DESC
) AS MANAGER
,max(ai.CALL_CENTER_NAME) KEEP (
dense_rank first ORDER BY ai.AGENT_ID DESC
) AS CALL_CENTER_NAME
,max(ai.CITY) KEEP (
dense_rank first ORDER BY ai.AGENT_ID DESC
) AS CITY
FROM agent_info ai
WHERE translate(ai.UID, 'X0123456789', 'X') IS NULL
GROUP BY ai.UID回答 1
Stack Overflow用户
发布于 2018-02-06 17:24:56
我认为您可以将其转换为包含单个DENSE_RANK()，然后对第一条记录执行一个MAX。
SELECT UID,
MAX(OWNER),
MAX(EMPLOYEE_KEY),
MAX(MANAGER),
MAX(CALL_CENTER_NAME),
MAX(CITY)
FROM (SELECT ai.UID,
ai.OWNER AS OWNER,
ai.EMPLOYEE_KEY AS EMPLOYEE_KEY,
ai.MANAGER_LOGIN AS MANAGER,
ai.CALL_CENTER_NAME AS CALL_CENTER_NAME,
ai.CITY AS CITY,
DENSE_RANK () OVER (PARTITION BY ai.UID ORDER BY ai.AGENT_ID DESC) rnk
FROM agent_info ai
WHERE TRANSLATE (ai.UID, 'X0123456789', 'X') IS NULL)
WHERE rnk = 1
GROUP BY UID;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48638845
复制相关文章
相似问题
腾讯云开发者
