分组技术和数据库

Question

早上好，我是初级数据库程序员。

我想用最短的方法来写这段代码。

我想让大家看看我是这样被处决的

fyear     money
<2012     4
2012      5
2013      5
2014      5
total     19

我有这样的密码

SELECT fyear,SUM(money)
FROM (

          SELECT       CASE 
                         WHEN fyear < 2012 THEN ' <2012'
                         WHEN fyear = 2012 THEN   '2012'
                         WHEN fyear = 2013 THEN   '2013'
                         WHEN fyear = 2014 THEN   '2014'
                         END fyear, money

              FROM( 
                    SELECT fyear, money
                    FROM   table1
                    UNION ALL
                    SELECT fyear, money
                    FROM   table2
                   )
 )GROUP BY fyear

上面的代码

fyear     money
<2012     4
2012      5
2013      5
2014      5

这是失踪的总数。我不确定如何添加“总计”，在代码中添加“总计”的最佳方法是什么？

非常感谢

Answer 1

你可能需要一个卷起

select NVL(fyear, 'total') fyear, money 
from (
      SELECT fyear, SUM(money) money
      FROM (SELECT CASE
                      WHEN fyear < 2012 THEN '<2012'
                      WHEN fyear = 2012 THEN '2012'
                      WHEN fyear = 2013 THEN '2013'
                      WHEN fyear = 2014 THEN '2014' 
                   END fyear, money
              FROM (SELECT fyear, money FROM table1
                    UNION ALL
                    SELECT fyear, money FROM table2
                   )
           )
      GROUP BY rollup(fyear)
     )

这意味着：

FYEAR       MONEY
------ ----------
<2012           4
2012            5
2013            5
2014            5
total          19

另外，根据您的场景，您的CASE可能可以简化：

CASE
   WHEN fyear < 2012 THEN '<2012'
   ELSE to_char(fyear)
END 

Answer 2

您可以使用一个联合来添加一个总行，如下所示；

with line_tots AS
(SELECT fyear, SUM (curr_qty) as curr_qty
FROM   (SELECT CASE
                  WHEN fyear < 2012 THEN ' <2012'
                  WHEN fyear = 2012 THEN '2012'
                  WHEN fyear = 2013 THEN '2013'
                  WHEN fyear = 2014 THEN '2014'
               END
                  fyear,
               curr_qty
        FROM  table_1)
GROUP BY fyear
ORDER BY fyear)
SELECT *
  FROM (
        SELECT fyear, curr_qty
        FROM line_tots
        UNION ALL
        SELECT 'total', SUM(curr_qty)
        FROM line_tots
        )
ORDER BY fyear 

(我删除了从两个表中选择的子查询，这对于解决方案来说是不必要的)

如果使用的是SQL*Plus，则另一种方法是使用计算报表列，该列不需要对查询进行更改。

SQL> compute sum label total of curr_qty on report
SQL> break on curr_qty on report
SQL> l
  1  SELECT fyear, SUM (curr_qty) as curr_qty
  2  FROM   (SELECT CASE
  3                    WHEN fyear < 2012 THEN ' <2012'
  4                    WHEN fyear = 2012 THEN '2012'
  5                    WHEN fyear = 2013 THEN '2013'
  6                    WHEN fyear = 2014 THEN '2014'
  7                 END
  8                    fyear,
  9                 curr_qty
 10          FROM  table_1)
 11  GROUP BY fyear
 12* ORDER BY fyear
SQL> /

FYEAR    CURR_QTY
------ ----------
 <2012         13
2012           10
2013           12
2014            9
       ----------
total          44