sql - 如何在 PL/SQL Oracle 中获取每年日期范围的数字总和

Question

我有一个查询。我想获得每年的数量总和，例如 2010、2011、2012、2013。它适用于
a.sale_date between '01-JAN-2010' and '31-DEC-2010'

但不是为了
a.sale_date between '01-JAN-2010' and '31-DEC-2018'

  SELECT c.tnama, SUM (a.net_weight)
    FROM sales a,
         invoice b,
         buy c,
         estate d
   WHERE     a.sale_date BETWEEN '01-JAN-2010' AND '31-DEC-2018'
         AND b.estate_code = d.estate_code
         AND d.estate_code = '023'
         AND b.actual_sale_year = a.sale_year
         AND b.actual_sale_no = a.sale_no
         AND b.lot_no = a.lot_no
         AND c.t$cuno = a.buyer_code
GROUP BY c.tnama
ORDER BY 1, 2 DESC

标签： sqloracle

Answer 1

获取每年的数量总和

意味着您实际上应该从列中选择那一年SALE_DATE并将其包含在GROUP BY子句中。

此外，虽然与您的问题无关，但您应该DATE在子句中使用数据类型值WHERE，例如

• 日期文字，例如date '2018-12-31'，或
• TO_DATE函数，例如to_date('31.12.2018', 'dd.mm.yyyy')，
• 但不是字符串，例如'01-JAN-2010'

---

  SELECT c.tnama, 
         EXTRACT (YEAR FROM a.sale_date) c_year,    --> this 
         SUM (a.net_weight)
    FROM sales a,
         invoice b,
         buy c,
         estate d
   WHERE     a.sale_date BETWEEN DATE '2010-01-01'  --> use dates, not strings 
                             AND DATE '2018-12-31'
         AND b.estate_code = d.estate_code
         AND d.estate_code = '023'
         AND b.actual_sale_year = a.sale_year
         AND b.actual_sale_no = a.sale_no
         AND b.lot_no = a.lot_no
         AND c.t$cuno = a.buyer_code
GROUP BY c.tnama, 
         EXTRACT (YEAR FROM a.sale_date)            --> has to be included into the GROUP BY clause
ORDER BY 1, 2 DESC