我try 将按T.ID分组的状态计数行返回到T.ID的单行输出中
我曾try 使用LISTAGG，但由于分组错误而陷入困境.
基本的SQL如下:
SELECT T.ID ,T.STATUS ||':'||COUNT(T.STATUS)||'; '
FROM TRANSACTION T
GROUP BY T.STATUS, T.ID;
因此，这将返回许多行(正如预期的那样)，但我希望具有相同T.ID的返回返回单行，如下所示:
| ID | RESULTS |
|---|---|
| 1000 | C:1 |
| 1231 | C:2, S:3, E:1 |
汇总两次-首先使用ID和STATUS生成计数，然后汇总每个ID的所有状态和计数:
SELECT ID,
LISTAGG(status || ':' || status_count, ', ')
WITHIN GROUP (ORDER BY status) AS results
FROM (
SELECT ID,
STATUS,
COUNT(STATUS) AS status_count
FROM TRANSACTION
GROUP BY
ID,
STATUS
)
GROUP BY
ID;
其中，对于样本数据:
CREATE TABLE transaction (ID, status) AS
SELECT 1000, 'C' FROM DUAL UNION ALL
SELECT 1231, 'C' FROM DUAL UNION ALL
SELECT 1231, 'C' FROM DUAL UNION ALL
SELECT 1231, 'S' FROM DUAL UNION ALL
SELECT 1231, 'S' FROM DUAL UNION ALL
SELECT 1231, 'S' FROM DUAL UNION ALL
SELECT 1231, 'E' FROM DUAL;
输出:
| ID | RESULTS |
|---|---|
| 1000 | C:1 |
| 1231 | C:2, E:1, S:3 |