主页 / user-3687706

Vitaly's questions

Martin Hope
Vitaly
Asked: 2024-09-24 16:50:04 +0800 CST
  • 8

我正在使用 oracle 19.24。我正在调试我的 SQL 查询并发现了一些奇怪的事情。以下是测试脚本：

create table test_payment
(
  payment_date date,
  payment_sum number
);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-03-2015', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-10-2019', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-10-2019', 'dd-mm-yyyy'), 117);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-11-2024', 'dd-mm-yyyy'), 27);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-09-2020', 'dd-mm-yyyy'), 369);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-07-2020', 'dd-mm-yyyy'), 199);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-08-2023', 'dd-mm-yyyy'), 118);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-04-2016', 'dd-mm-yyyy'), 48);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-09-2019', 'dd-mm-yyyy'), 239);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-05-2021', 'dd-mm-yyyy'), 78);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-02-2018', 'dd-mm-yyyy'), 265);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-01-2019', 'dd-mm-yyyy'), 89);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-12-2021', 'dd-mm-yyyy'), 175);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-04-2024', 'dd-mm-yyyy'), 249);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-06-2015', 'dd-mm-yyyy'), 125);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-06-2021', 'dd-mm-yyyy'), 3);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-02-2024', 'dd-mm-yyyy'), 55);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-04-2018', 'dd-mm-yyyy'), 30);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-11-2020', 'dd-mm-yyyy'), 259);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-06-2016', 'dd-mm-yyyy'), 279);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-10-2024', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-02-2021', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-09-2020', 'dd-mm-yyyy'), 58);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-01-2024', 'dd-mm-yyyy'), 38);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-09-2020', 'dd-mm-yyyy'), 76);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-07-2017', 'dd-mm-yyyy'), 44);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-07-2021', 'dd-mm-yyyy'), 14);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-10-2019', 'dd-mm-yyyy'), 159);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('29-03-2023', 'dd-mm-yyyy'), 118);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('19-11-2022', 'dd-mm-yyyy'), 135);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-12-2018', 'dd-mm-yyyy'), 19);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-12-2018', 'dd-mm-yyyy'), 45);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-05-2015', 'dd-mm-yyyy'), 14);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('04-03-2022', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-03-2018', 'dd-mm-yyyy'), 74);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('07-06-2015', 'dd-mm-yyyy'), 129);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-08-2019', 'dd-mm-yyyy'), 51);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-12-2024', 'dd-mm-yyyy'), 6);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('03-03-2024', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('14-04-2017', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('16-07-2016', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-11-2015', 'dd-mm-yyyy'), 115);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-12-2022', 'dd-mm-yyyy'), 135);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-06-2017', 'dd-mm-yyyy'), 45);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('31-08-2023', 'dd-mm-yyyy'), 327);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-06-2020', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-10-2023', 'dd-mm-yyyy'), 189);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-12-2019', 'dd-mm-yyyy'), 225);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('22-02-2015', 'dd-mm-yyyy'), 25);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-02-2017', 'dd-mm-yyyy'), 199);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('06-03-2021', 'dd-mm-yyyy'), 5);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-03-2022', 'dd-mm-yyyy'), 79);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-02-2022', 'dd-mm-yyyy'), 219);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-06-2024', 'dd-mm-yyyy'), 30);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('30-04-2018', 'dd-mm-yyyy'), 369);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-06-2022', 'dd-mm-yyyy'), 209);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-07-2023', 'dd-mm-yyyy'), 499);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-11-2023', 'dd-mm-yyyy'), 130);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('01-10-2020', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-12-2022', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-11-2017', 'dd-mm-yyyy'), 175);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-09-2018', 'dd-mm-yyyy'), 255);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-08-2020', 'dd-mm-yyyy'), 255);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-01-2019', 'dd-mm-yyyy'), 179);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('07-01-2015', 'dd-mm-yyyy'), 105);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-09-2017', 'dd-mm-yyyy'), 48);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-09-2021', 'dd-mm-yyyy'), 115);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-08-2023', 'dd-mm-yyyy'), 659);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-06-2019', 'dd-mm-yyyy'), 13);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-09-2015', 'dd-mm-yyyy'), 595);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-03-2015', 'dd-mm-yyyy'), 68);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('10-04-2017', 'dd-mm-yyyy'), 71);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('06-04-2023', 'dd-mm-yyyy'), 28);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('01-04-2018', 'dd-mm-yyyy'), 179);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('02-08-2015', 'dd-mm-yyyy'), 159);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('14-11-2023', 'dd-mm-yyyy'), 1);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-06-2020', 'dd-mm-yyyy'), 125);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-04-2023', 'dd-mm-yyyy'), 51);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('11-10-2017', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('17-03-2022', 'dd-mm-yyyy'), 229);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-08-2021', 'dd-mm-yyyy'), 29);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-06-2017', 'dd-mm-yyyy'), 38);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('08-07-2024', 'dd-mm-yyyy'), 249);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-11-2022', 'dd-mm-yyyy'), 65);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('05-12-2018', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('26-10-2018', 'dd-mm-yyyy'), 129);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('20-06-2024', 'dd-mm-yyyy'), 35);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('29-11-2020', 'dd-mm-yyyy'), 29);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('18-06-2022', 'dd-mm-yyyy'), 209);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('25-04-2024', 'dd-mm-yyyy'), 119);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-12-2020', 'dd-mm-yyyy'), 299);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('03-02-2024', 'dd-mm-yyyy'), 238);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('27-06-2016', 'dd-mm-yyyy'), 89);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-09-2023', 'dd-mm-yyyy'), 69);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('13-04-2021', 'dd-mm-yyyy'), 20);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('15-10-2016', 'dd-mm-yyyy'), 59);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('12-09-2016', 'dd-mm-yyyy'), 15);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('09-10-2018', 'dd-mm-yyyy'), 149);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('21-10-2016', 'dd-mm-yyyy'), 85);

insert into test_payment (PAYMENT_DATE, PAYMENT_SUM)
values (to_date('28-06-2019', 'dd-mm-yyyy'), 24);

此查询返回 12482，没有什么特别的

select
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)

但是这个查询返回9003！

select
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)
pivot
(
  sum(payment_sum) for "year" in (2020,2021,2022,2023,2024)
)

这个想法显然是这样的：

select
sum("2020"),sum("2021"),sum("2022"),sum("2023"),sum("2024"),
sum(for_total)
from (
  select
    extract(year from payment_date) "year",
    payment_sum for_total,
    payment_sum
  from test_payment
)
pivot
(
  sum(payment_sum) for "year" in (2020,2021,2022,2023,2024)
)

最后得到过去 5 年的总和（不仅是 5 年的总和，还有总和）。顺便说一下，过去 5 年的总和是 7000。

为什么pivot会改变总和？ 看来我对pivot有些不了解或不理解。

sql