如何在嵌套SQL查询中新增PRODUCT_ID字段获取库存数据？

表结构 #

-----------
| METERS  |
-----------
| METER_ID|
| PARK_ID |
| ZONE_ID |
-----------

----------------------
| MT_PRODUCT_DETAILS |
----------------------
| PRODUCT_ID         |
| PRODUCT_TITLE      |
| DESCRIPTION        |
-----------------------         

-----------------------
| MT_AS_EVENT_CURRENT |
-----------------------
| DEVICE_ID           |
| MORE_DATA           |
| PRODUCT_ID          |
| STOCK_PRODUCT       |
| STATUS              |
| DATE_DEVICE         |
| EVENT_TYPE_ID       |
-------------------------

原查询问题 #

原嵌套SQL仅能获取PRODUCT_TITLE和STOCK，尝试在外层直接添加PRODUCT_ID失败，需要修改查询以同时获取PRODUCT_ID、PRODUCT_TITLE及按产品分组的库存总数。

原查询代码：

SELECT 
    PRODUCT_TITLE, SUM(REMAINING_STOCK) STOCK 
FROM
    (SELECT 
         PRODUCT_TITLE, REMAINING_STOCK 
     FROM 
         (SELECT 
              PRODUCT_ID, SUM(REMAINING_STOK) REMAINING_STOCK 
          FROM 
              (SELECT 
                   METER_ID, PRODUCT_ID, MAX(REMAINING_STOCK) keep
            (DENSE_RANK last ORDER BY DATE_DEVICE) REMAINING_STOCK  
               FROM 
                   (SELECT 
                        METERS.METER_ID, DATE_DEVICE, PRODUCT_ID, STOCK_PRODUCT REMAINING_STOCK 
                    FROM 
                        MT_AS_EVENT_CURRENT EV 
                    INNER JOIN
                        METERS ON EV.DEVICE_ID = METERS.METER_ID
                    WHERE 
                        EVENT_TYPE_ID = 1111
                        AND EV.DATE_DEVICE BETWEEN SYSDATE - 60 AND SYSDATE)
               GROUP BY
                   METER_ID, PRODUCT_ID)
        GROUP BY
            PRODUCT_ID) STOCKS 
    INNER JOIN
        MT_PRODUCT_DETAILS PRODUCT ON STOCKS.PRODUCT_ID = PRODUCT.PRODUCT_ID) 
GROUP BY 
    PRODUCT_TITLE

修改后的查询 #

核心修改点：

• 在中间子查询STOCKS中保留PRODUCT_ID，确保能传递到外层
• 外层查询添加PRODUCT_ID，并将其加入GROUP BY（由于PRODUCT_ID与PRODUCT_TITLE是一一对应关系，不会改变分组统计结果）
• 修正原查询中REMAINING_STOK的拼写错误

SELECT 
    PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_TITLE, SUM(STOCKS.REMAINING_STOCK) STOCK 
FROM
    (SELECT 
         PRODUCT_ID, SUM(REMAINING_STOCK) REMAINING_STOCK 
     FROM 
         (SELECT 
              METER_ID, PRODUCT_ID, MAX(REMAINING_STOCK) KEEP
            (DENSE_RANK LAST ORDER BY DATE_DEVICE) REMAINING_STOCK  
           FROM 
               (SELECT 
                    METERS.METER_ID, DATE_DEVICE, PRODUCT_ID, STOCK_PRODUCT REMAINING_STOCK 
                FROM 
                    MT_AS_EVENT_CURRENT EV 
                INNER JOIN
                    METERS ON EV.DEVICE_ID = METERS.METER_ID
                WHERE 
                    EVENT_TYPE_ID = 1111
                    AND EV.DATE_DEVICE BETWEEN SYSDATE - 60 AND SYSDATE)
           GROUP BY
               METER_ID, PRODUCT_ID)
    GROUP BY
        PRODUCT_ID) STOCKS 
INNER JOIN
    MT_PRODUCT_DETAILS PRODUCT ON STOCKS.PRODUCT_ID = PRODUCT.PRODUCT_ID
GROUP BY 
    PRODUCT.PRODUCT_ID, PRODUCT.PRODUCT_TITLE

说明 #

移除了多余的嵌套子查询层，直接将STOCKS子查询与MT_PRODUCT_DETAILS关联，既减少了嵌套层级，又保证PRODUCT_ID能被外层查询获取到；同时将PRODUCT_ID加入分组条件，确保统计逻辑符合要求。

内容的提问来源于stack exchange，提问作者NewUser