时间戳

首页 > 解决方案 > Oracle 如何拆分 SELECT 结果？

sql - Oracle 如何拆分 SELECT 结果？

问题描述

我有表格，我想按代码列分隔/分块每个部分。

我目前的结果：

Sent Date     code   emailsent
31/08/2021    0001   1  
31/08/2021    0002   2  
31/08/2021    0003   2 
31/08/2021    0004   2

预期结果：

31/08/2021    0001   1 
30/08/2021    0001   2  
29/08/2021    0001   2 
28/08/2021    0001   2 
----------------------   skipped 27th to 2nd records
01/08/2021    0001   2

然后加载下一批，因为它是一个新代码，我们这样做直到每个给定月份读取所有代码。计数必须始终保持不变，因为我需要将电子邮件计数为核心目标。

31/08/2021    0002   3 
30/08/2021    0002   9  
29/08/2021    0002   3 
28/08/2021    0002   10 
----------------------   skipped 27th to 2nd records
01/08/2021    0002   9

SELECT 
    TRUNC(E.DATE) "Sent Date", o.CODE "code", COUNT(*) "Emails Sent"
FROM 
    emails ec, orders so, office o
WHERE 
    EC.OID = SO.OID
    AND SO.CODE = O.CODE
    AND E.DATE BETWEEN TO_DATE('01/AUG/2021','dd/mm/yyyy') 
    AND TO_DATE('31/AUG/2021','dd/mon/yyyy')
GROUP BY
    TRUNC(E.DATE), SO.CODE
ORDER BY
     TRUNC(E.DATE) DESC, SO.CODE;

这个概念叫什么？

修复我只更改了最后一行：

TRUNC(E.DATE) DESC, SO.CODE;

至

SO.CODE, TRUNC(E.DATE) DESC;

标签： sqloracle

解决方案

按照我的理解，它是您需要的日历（因为您拥有的表格不包含所有日期）。像这样的东西（阅读代码中的注释）：

SQL> with
  2  calendar as
  3    -- quick & dirty first 10 days of this year's August
  4    (select date '2021-08-01' + level - 1 as datum
  5     from dual
  6     connect by level <= 10
  7    ),
  8  all_codes (code) as
  9    -- you probably have such a table; you need a distinct set of codes
 10    -- to cross join it to CALENDAR
 11    (select '0001' from dual union all
 12     select '0002' from dual union all
 13     select '0003' from dual
 14    ),
 15  emails (code, datum, emails_sent) as
 16    -- simplified; you don't have such a data, but - as you didn't
 17    -- post test case, I'm not going to recreate ALL of your tables
 18    (select '0001', date '2021-08-03', 9 from dual union all
 19     select '0001', date '2021-08-08', 2 from dual union all
 20     select '0002', date '2021-08-05', 1 from dual union all
 21     select '0003', date '2021-08-04', 7 from dual
 22    )
 23  -- final query
 24  select c.datum,
 25         a.code,
 26         nvl(e.emails_sent, 0) emails_sent
 27  from calendar c cross join all_codes a
 28    left join emails e on e.datum = c.datum and e.code = a.code
 29  order by a.code, c.datum desc;

结果：

DATUM      CODE EMAILS_SENT
---------- ---- -----------
10.08.2021 0001           0
09.08.2021 0001           0
08.08.2021 0001           2
07.08.2021 0001           0
06.08.2021 0001           0
05.08.2021 0001           0
04.08.2021 0001           0
03.08.2021 0001           9
02.08.2021 0001           0
01.08.2021 0001           0

DATUM      CODE EMAILS_SENT
---------- ---- -----------
10.08.2021 0002           0
09.08.2021 0002           0
08.08.2021 0002           0
07.08.2021 0002           0
06.08.2021 0002           0
05.08.2021 0002           1
04.08.2021 0002           0
03.08.2021 0002           0
02.08.2021 0002           0
01.08.2021 0002           0

DATUM      CODE EMAILS_SENT
---------- ---- -----------
10.08.2021 0003           0
09.08.2021 0003           0
08.08.2021 0003           0
07.08.2021 0003           0
06.08.2021 0003           0
05.08.2021 0003           0
04.08.2021 0003           7
03.08.2021 0003           0
02.08.2021 0003           0
01.08.2021 0003           0

30 rows selected.

SQL>

推荐阅读