oracle 使用分组集的数据库查询

我正在使用Oracle和SQL Developer。我已经下载了方案Sales History，需要使用它进行一些查询。我正在使用表PRODUCTS。作为用户，我需要每个PROD_CATEGORY、PROD_SUBCATEGORY、SUPPLIER_ID的PROD_LIST_PRICE的总和，以及每个PROD_CATEGORY的总和。我需要通过GROUPING SETS提供解决方案。我做了类似的事情，但似乎是完全错误的：

WITH ds as(
  SELECT prod_category, prod_subcategory, supplier_id, sum(prod_list_price) sum_all,
GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
FROM products
GROUP BY 
GROUPING SETS (
                (prod_category, prod_subcategory, supplier_id), ()
                )
)
SELECT decode ( GROUPING (prod_category), 1, 'Total', 0, prod_category) cat,
SUM(prod_list_price) sum_prod
FROM ds
GROUP BY 
GROUPING SETS( (prod_category), ())
ORDER BY prod_category;

也许我没有正确理解条件。如何修复此查询或进行修改以完全满足我的需要？

它不能以当前形式运行

SQL> WITH ds as(
  2    SELECT prod_category, prod_subcategory, supplier_id, sum(prod_list_price) sum_all,
  3  GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
  4  FROM sh.products
  5  GROUP BY
  6  GROUPING SETS (
  7                  (prod_category, prod_subcategory, supplier_id), ()
  8                  )
  9  )
 10  SELECT decode ( GROUPING (prod_category), 1, 'Total', 0, prod_category) cat,
 11  SUM(prod_list_price) sum_prod
 12  FROM ds
 13  GROUP BY
 14  GROUPING SETS( (prod_category), ())
 15  ORDER BY prod_category;
SUM(prod_list_price) sum_prod
    *
ERROR at line 11:
ORA-00904: "PROD_LIST_PRICE": invalid identifier

但是分组集的目的是让您准确地选择要进行的分类汇总级别，因此很少需要一个以上的规范。
如果我没阅读你的要求

我需要每个产品类别、产品子类别、供应商标识的产品列表价格总和，以及每个产品类别的总和 *

下面的代码应该可以做到这一点

SQL> SELECT
  2    prod_category,
  3    prod_subcategory,
  4    supplier_id,
  5    sum(prod_list_price) tot,
  6    GROUPING_ID (prod_category, prod_subcategory, supplier_id) group_id
  7  FROM sh.products
  8  GROUP BY
  9  GROUPING SETS (
 10                  (prod_category, prod_subcategory, supplier_id),
 11                  (prod_category)
 12                  );

PROD_CATEGORY                                      PROD_SUBCATEGORY                                   SUPPLIER_ID        TOT   GROUP_ID
-------------------------------------------------- -------------------------------------------------- ----------- ---------- ----------
Photo                                              Cameras                                                      1     899.99          0
Photo                                              Camcorders                                                   1    1099.99          0
Photo                                              Camera Media                                                 1     155.97          0
Photo                                              Camera Batteries                                             1     145.95          0
Photo                                                                                                                 2301.9          3
Hardware                                           Desktop PCs                                                  1     999.99          0
Hardware                                           Portable PCs                                                 1    1299.99          0
Hardware                                                                                                             2299.98          3
Electronics                                        Home Audio                                                   1    1099.98          0
Electronics                                        Y Box Games                                                  1     152.92          0
Electronics                                        Game Consoles                                                1     299.99          0
Electronics                                        Y Box Accessories                                            1      28.98          0
Electronics                                                                                                          1581.87          3
Software/Other                                     Accessories                                                  1     108.95          0
Software/Other                                     Documentation                                                1     269.94          0
Software/Other                                     Recordable CDs                                               1      86.93          0
Software/Other                                     Operating Systems                                            1     199.99          0
Software/Other                                     Bulk Pack Diskettes                                          1      44.98          0
Software/Other                                     Recordable DVD Discs                                         1     105.95          0
Software/Other                                                                                                        816.74          3
Peripherals and Accessories                        CD-ROM                                                       1     254.94          0
Peripherals and Accessories                        Memory                                                       1     262.98          0
Peripherals and Accessories                        Monitors                                                     1    1899.98          0
Peripherals and Accessories                        Modems/Fax                                                   1      90.98          0
Peripherals and Accessories                        Accessories                                                  1     189.95          0
Peripherals and Accessories                        Printer Supplies                                             1     347.96          0
Peripherals and Accessories                                                                                          3046.79          3

oracle 使用分组集的数据库查询

1条答案

相关问题

热门标签

最新问答