月度报表PLSQL

Question

我正在使用PLSQL，并试图从下表(税收)中创建月、月和年报表：

Date            District         City          Amount
2020-01-02       West            Auckland      1000
2020-01-03       East            Auckland      1200
2020-01-04       North           Auckland      1300
2020-02-02       West            Auckland       500
2020-02-03       East            Auckland       300
2020-02-04       North           Auckland       200
2020-01-02       West            Wellington    2000
2020-01-03       East            Wellington     500
2020-01-04       North           Wellington    2500
2020-02-02       West            Wellington     300
2020-02-03       East            Wellington    1000
2020-02-04       North           Wellington     500

我想得到的是由伦敦金融城提供的最近一天、最近一个月和今年的一个小组(大致上是总结)如下：

City             2020-02-04   Month_To_Date    Year_To_Date
Auckland           200         1000             3000
Wellington         500         2800             7800

是否有使用Oracle PLSQL进行此操作的方法？

Answer 1

您想要的基本内容是一个相对简单的查询。

select city      
      , <current_date>  
      , sum(amount) amt 
   from tax
   cross join for_date
   where tax_date = <current_date>
   group by city, <current_date>  

但是，对于总数的每个子集(以上amt)，这需要稍加修改，因为

tax_date = ，需要根据不同的日期进行调整。此外，由于这些不同的条件，您不能在单个选择中执行此操作。解决方案是为每个日期范围构建一个CTE，然后连接在一起。

(注意:我将变量/列名更改为tax_date，因为日期是具有set定义和保留字的数据类型。使用它作为变量/列名是非常糟糕的做法。提示：从不使用数据类型作为对象名)。)

with tax   as
  ( select date '2020-01-02' tax_date
         , 'West' district,'Auckland' city
         , 1000 amount 
      from dual union all                                   
    select date '2020-01-03','East','Auckland',1200    from dual union all 
    select date '2020-01-04','North','Auckland',1300   from dual union all
    select date '2020-02-02','West','Auckland',500     from dual union all
    select date '2020-02-03','East','Auckland',300     from dual union all
    select date '2020-02-04','North','Auckland',200    from dual union all
    select date '2020-01-02','West','Wellington',2000  from dual union all
    select date '2020-01-03','East','Wellington',500   from dual union all
    select date '2020-01-04','North','Wellington',2500 from dual union all
    select date '2020-02-02','West','Wellington',300   from dual union all
    select date '2020-02-03','East','Wellington',1000  from dual union all
    select date '2020-02-04','North','Wellington',500  from dual 
   )
   , for_date as (select date '&current_date' dt from dual)
   , dly as 
          ( select city      
                 , dt   
                 , sum(amount) amt 
              from tax
              cross join for_date
             where tax_date = dt 
             group by city, dt              
          )
   , mtd as 
          ( select city      
                 , dt   
                 , sum(amount) amt
              from tax                 
             cross join for_date             
             where trunc(tax_date,'mm') = trunc(dt,'mm') 
               and tax_date <= dt
             group by city, dt
          )
   , ytd as 
          ( select city      
                 , dt   
                 , sum(amount) amt 
              from tax
              cross join for_date             
             where trunc(tax_date,'yyyy') = (select trunc(dt,'yyyy') from for_date)
               and tax_date <= dt 
             group by city, dt
          )
select  
       dly.city       "City"
     , dly.dt         "Tax Date"
     , dly.amt        "Daily Amount"
     , mtd.amt        "Month to Date"
     , ytd.amt        "Year To Date"
  from dly
  join mtd on (dly.city = mtd.city)
  join ytd on (dly.city = ytd.city)
 order by dly.city;    

差异：结果将当前日期添加为列，而不是将其作为列名进行处理。对于SQL，这是主要的跳转(如果它甚至可行的话)。但是对于表示层来说，这个转换应该非常简单。

顺便说一句:检查一下你的数学，它关了。例:惠灵顿到目前为止的月份是1800 (500+1000+300)，而不是2800，在例外的结果中表示。