时间戳

首页 > 解决方案 > 使用 GROUP BY 函数在 INNER JOINING 中执行 Sum 不工作 Postgresql

sql - 使用 GROUP BY 函数在 INNER JOINING 中执行 Sum 不工作 Postgresql

问题描述

任何人都可以解释为什么即使我已经使用了 sum 和 group by 子句，我仍然得到一个分隔的 2 个相同的列名？

这是我在 postgres 中的查询：

SELECT tbl_boxes.box_name                          AS "BOX NAME",
       tbl_stores.store_name                       AS "STORE NAME",
       tbl_sku.sap_name                            AS "SKU NAME",
       SUM(tbl_bad_orders_product.quantity_pcs)    AS "QUANTITY PER PIECE",
       SUM(tbl_bad_orders_product.price_per_piece) AS "PRICE PER PIECE",
       SUM(tbl_bad_orders_product.quantity_cs)     AS "QUANTITY PER CASE",
       SUM(tbl_bad_orders_product.price_per_case)  AS "PRICE PER CASE",
       tbl_bad_orders_product.reason               AS "REASON",
       SUM(tbl_bad_orders_product.total_amount)    AS "TOTAL AMOUNT",
       tbl_bad_orders_product.date_created         AS "DATE CREATED",
       tbl_bad_orders_product.date_updated         AS "DATE UPDATED",
       tbl_bad_orders_product.date_sync            AS "DATE SYNCED"
FROM tbl_bad_orders_product
       INNER JOIN tbl_users ON tbl_bad_orders_product.tbluserid = tbl_users.tbluserid
       INNER JOIN tbl_boxes ON tbl_bad_orders_product.tblboxid = tbl_boxes.tblboxesid
       INNER JOIN tbl_stores ON tbl_bad_orders_product.tblstoreid = tbl_stores.tblstoreid
       INNER JOIN tbl_sku ON tbl_bad_orders_product.tblskuid = tbl_sku.tblskuid
WHERE tbl_bad_orders_product.date_sync::date >= '2019-02-19'
  AND tbl_bad_orders_product.date_sync::date <= '2019-02-19'
GROUP BY tbl_boxes.box_name, tbl_stores.store_name, tbl_sku.sap_name, tbl_bad_orders_product.quantity_pcs,
         tbl_bad_orders_product.price_per_piece, tbl_bad_orders_product.quantity_cs,
         tbl_bad_orders_product.price_per_case, tbl_bad_orders_product.reason, tbl_bad_orders_product.total_amount,
         tbl_bad_orders_product.date_created, tbl_bad_orders_product.date_updated, tbl_bad_orders_product.date_sync
ORDER BY tbl_boxes.box_name ASC

但我得到这个输出：

在此处输入图像描述

请帮助我如何解决它。

标签： sqlpostgresql

解决方案

本质上，您在 tbl_bad_orders (quantity_pcs、price_per_piece、quantity_cs、price_per_case、total_amount) 中包含相同的聚合列，SELECT该子句几乎不需要聚合 ，因为您运行聚合函数，按其GROUP BY值SUM分组。

只需从子句中删除SUM聚合中使用的这些数字字段。GROUP BY此外，考虑表别名以提高可读性和可维护性。SELECT还考虑对结果集左侧的分组列和结果集右侧的聚合列重新排序。最后，您的WHERE情况可能是多余的。

SELECT b.box_name                  AS "BOX NAME",
       s.store_name                AS "STORE NAME",
       sku.sap_name                AS "SKU NAME",
       bad.reason                  AS "REASON",
       bad.date_created            AS "DATE CREATED",
       bad.date_updated            AS "DATE UPDATED",
       bad.date_sync               AS "DATE SYNCED",
       SUM(bad.quantity_pcs)       AS "QUANTITY PER PIECE",
       SUM(bad.price_per_piece)    AS "PRICE PER PIECE",
       SUM(bad.quantity_cs)        AS "QUANTITY PER CASE",
       SUM(bad.price_per_case)     AS "PRICE PER CASE",
       SUM(bad.total_amount)       AS "TOTAL AMOUNT"
FROM tbl_bad_orders_product AS bad
     INNER JOIN tbl_users AS u ON bad.tbluserid = u.tbluserid
     INNER JOIN tbl_boxes AS b ON bad.tblboxid = b.tblboxesid
     INNER JOIN tbl_stores AS s ON bad.tblstoreid = s.tblstoreid
     INNER JOIN tbl_sku AS sku ON bad.tblskuid = sku.tblskuid
WHERE bad.date_sync::date >= '2019-02-19'
  AND bad.date_sync::date <= '2019-02-19'
GROUP BY b.box_name, 
         s.store_name, 
         sku.sap_name, 
         bad.reason,
         bad.date_created, 
         bad.date_updated, 
         bad.date_sync
ORDER BY b.box_name ASC

推荐阅读