如何在 Oracle 中同时使用双倍分组依据和求和语句

Question

>我正在尝试使用下面的查询，但它给出了错误

SELECT s.LOCAL_CODE,substr(p.ACCOUNT_CREDIT,-3),(p.SUMMA/100) as profit
FROM OPERATIONS s INNER JOIN LEADS p ON s.PAY_ID = p.PAY_ID
WHERE s.date_paid >= TO_DATE('03.12.2019', 'DD.MM.YYYY')
AND s.date_paid < TO_DATE('03.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY
AND state = 'T'
AND s.filial_code = '006789'
AND SUBSTR(p.ACCOUNT_CREDIT, 1, 5) = '765294'
GROUP BY s.LOCAL_CODE,substr(p.ACCOUNT_CREDIT,-3);

Answer 1

如果LEADS.SUMMA有期望值，那么你不需要Group By子句，否则如果你使用Group By那么所有未分组的字段只能用作聚合函数的参数：

SELECT s.LOCAL_CODE
, Substr(p.ACCOUNT_CREDIT, -3)
, Sum(p.SUMMA)/100 as profit
FROM OPERATIONS s 
INNER JOIN LEADS p ON s.PAY_ID = p.PAY_ID
WHERE s.date_paid >= TO_DATE('03.12.2019', 'DD.MM.YYYY')
AND s.date_paid < TO_DATE('03.12.2019', 'DD.MM.YYYY') + INTERVAL '1' DAY
AND state = 'T'
AND s.filial_code = '006789'
AND SUBSTR(p.ACCOUNT_CREDIT, 1, 5) = '765294'
GROUP BY s.LOCAL_CODE
, substr(p.ACCOUNT_CREDIT, -3);