我是 SQL 新手，我不知道如何做到这一点。我想对类似的对（vin，action）求和一个名为“total_spending”的coulmn，并选择第一个dealer_name和参考月份年份（因此它不会创建重复项）并具有如下示例的输出：
输入
action dealer_name vin Total_spending reference month year
A1 D1 V1 T1 R1 M1 Y1
A2 D2 V2 T2 R1 M1 Y1
A2 D2 V2 T3 R2 M2 Y2
A3 D2 V1 T4 R1 M1 Y1
A4 D1 V2 T5 R1 M1 Y1
A2 D1 V2 T6 R1 M1 Y1
A1 D1 V1 T7 R2 M2 Y2
A4 D1 V2 T8 R2 M2 Y2
A1 D1 V1 T9 R3 M3 Y3
A3 D2 V2 T10 R1 M1 Y1
A3 D2 V1 T11 R2 M2 Y2
output
action dealer_name vin Total_spending reference month year
A1 D1 V1 T1 + T7 + T9 R1 M1 Y1
A2 D2 V2 T2 + T3 R1 M1 Y1
A3 D2 V1 T4 + T11 R1 M1 Y1
A4 D1 V2 T5 + T8 R1 M1 Y1
A2 D1 V2 T6 R1 M1 Y1
A3 D2 V2 T10 R1 M1 Y1
SELECT
action,
dealer_name,
vin,
SUM(total_spending) as total_spending,
reference,
year,
issue_date,
country_code
FROM
db_raw_irn_67634_vdt.sap_vme_pol
GROUP BY
action,
dealer_name,
vin,
reference,
year,
issue_date,
country_code
如果你的RDBMS支持窗口函数，你可以做一个窗口SUM()并使用ROW_NUMBER()选择要显示的相关记录：
SELECT
action,
dealer_name,
vin,
total_spending,
reference,
month,
year
FROM (
SELECT
action,
dealer_name,
vin,
SUM(total_spending) OVER(PARTITION BY action, dealer_name, vin) total_spending,
ROW_NUMBER() OVER(PARTITION BY action, dealer, vin ORDER BY reference, year, month) rn,
reference,
month,
year
FROM mytable
) x
WHERE rn = 1
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
