在Oracle SQL中使用GROUP BY时的ROWNUM

Question

我需要构建一个查询，按成员和到期日期分组检索信息，但我需要为每个成员添加一个序列号。

例如：

如果成员"A"有3条记录将到期，"B"只有1条，"C"有2条，那么我需要一个如下的结果：

序号 成员 有效日期 1 A 01/01/2020 2 A 02/01/2020 3 A 03/01/2020 1 B 01/01/2020 1 C 01/01/2020 2 C 02/01/2020

我的查询语句如下：

SELECT ROW_NUMBER() OVER(ORDER BY TRUNC(EXPIRATION_DT) ASC) AS SEQUENCE, MEMBER_ID AS MEMBER, SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) AS POINTS, trunc(EXPIRATION_DT) AS EXPDATE
FROM TABLE1
WHERE EXPIRATION_DT > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID, TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;

但是我无法“分组”序列号... 现在的结果是:

序列号 记忆点 得分 日期
1   1-O  188   2018-03-01 00:00:00
2   1-C  472   2018-03-01 00:00:00
3   1-A  485   2018-03-01 00:00:00
4   1-1  267   2018-03-01 00:00:00
5   1-E  500   2018-03-01 00:00:00
6   1-P  55    2018-03-01 00:00:00
7   1-E  14    2018-03-01 00:00:00

请注意，这里保留了HTML标签。

- LPS

---

请问，您的实际结果中的“Mem”列是什么意思，如何将其转换为“A”，“B”，“C”？ - Dmitriy

A、B 或 C 只是可能的值的示例... - LPS

Answer 1

我认为您需要使用DENSE_RANK窗口函数。尝试这个 -

 SELECT DENSE_RANK() OVER (PARTITION BY MEMBER ORDER BY TRUNC(EXPIRATION_DT) ASC) AS SEQUENCE
       ,MEMBER_ID AS MEMBER
       ,SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) AS POINTS
       ,trunc(EXPIRATION_DT) AS EXPDATE
FROM TABLE1
WHERE EXPIRATION_DT > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;

- Ankit Bajpai

Answer 2

with g as (
select *
From TABLE1 g
group by MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0   ---- etc
) 
select rownum, g.* From g

此选择返回带有序号的第一列

- Igor