时间戳

首页 > 解决方案 > 计算第一行的 SUM 和零总重复项

sql - 计算第一行的 SUM 和零总重复项

问题描述

我有使用 windows 函数的 sql 查询，我设法计算表 2 中的记录总和，但是我无法从表 1 中删除总数的重复项并仅在第 1 行中显示总和。

询问：

SELECT --row_number() over (partition by TABLE01.ITEMID order by TABLE01.ITEMID DESC) as rownumber,
TABLE01.ITEMID, TABLE01.WAREHOUSE, TABLE01.LOCATION, TABLE01.INVENTORY AS TABLE01INVENTORY, TABLE02.ITEMID, 
Table02.WAREHOUSE, TABLE02.LOCATION,Table02.LOCATIONNAME,table02.inventory AS TABLE02INVENTORY,
--(coalesce(table02.inventory,0)) AS TABLE02INVENTORY,
SUM(TABLE02.inventory) OVER(PARTITION BY TABLE01.ITEMID order by TABLE01.ITEMID  ) as SUMTABLE02INVENTORY--,
,(TABLE01.inventory-SUM(TABLE02.inventory) OVER(PARTITION BY TABLE01.ITEMID order by TABLE01.ITEMID )) AS VARBYSUM 
FROM Table01 LEFT JOIN Table02 ON Table01.ITEMID = Table02.ITEMID and Table01.warehouse = table02.warehouse 
GROUP BY TABLE01.ITEMID, TABLE01.WAREHOUSE, TABLE01.LOCATION, TABLE01.INVENTORY, TABLE02.ITEMID, 
Table02.WAREHOUSE, TABLE02.LOCATION, Table02.LOCATIONNAME,table02.inventory

输出： 

+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目 | 仓库 | 位置 | TABLE01库存 | 项目 | 仓库 | 位置 | 位置名称 | TABLE02库存 | SUMTABLE02库存 | VARBYSUM |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 5 | 项目001 | WHS001 | T01 | 技术001 | 1 | 6 | -1 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 5 | 项目001 | WHS001 | T02 | 技术002 | 2 | 6 | -1 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 5 | 项目001 | WHS001 | T03 | 技术003 | 3 | 6 | -1 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目002 | WHS001 | LOC002 | 4 | 项目002 | WHS001 | T01 | 技术001 | 1 | 4 | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目002 | WHS001 | LOC002 | 4 | 项目002 | WHS001 | T02 | 技术002 | 3 | 4 | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目003 | WHS001 | LOC003 | 2 | 空 | 空 | 空 | 空 | 空 | 空 | 空 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --

预期输出：

+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目 | 仓库 | 位置 | TABLE01库存 | 项目 | 仓库 | 位置 | 位置名称 | TABLE02库存 | SUMTABLE02库存 | VARBYSUM |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 5 | 项目001 | WHS001 | T01 | 技术001 | 1 | 6 | -1 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 0 | 项目001 | WHS001 | T02 | 技术002 | 2 | 0 | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目001 | WHS001 | LOC001 | 0 | 项目001 | WHS001 | T03 | 技术003 | 3 | | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目002 | WHS001 | LOC002 | 4 | 项目002 | WHS001 | T01 | 技术001 | 1 | 4 | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目002 | WHS001 | LOC002 | 0 | 项目002 | WHS001 | T02 | 技术002 | 3 | 0 | 0 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --+
| 项目003 | WHS001 | LOC003 | 2 | 空 | 空 | 空 | 空 | 空 | 空 | 空 |
+---------+------------+----------+---------------- --+---------+------------+----------+-------------- +------------------+----------+-------- --

标签： sql

解决方案

如果我理解正确，您可以为此使用ROW_NUMBER()和CASE表达。下面显示了该TABLE01INVENTORY列：

SELECT t1.ITEMID, t1.WAREHOUSE, t1.LOCATION,  
       (CASE WHEN 1 = row_number() over (partition by t1.ITEMID , t1.warehouse, t1.location order by t2.location)
             THEN t1.INVENTORY
        END) AS TABLE01INVENTORY,
       t2.ITEMID, t2.WAREHOUSE, t2.LOCATION,
       t2.LOCATIONNAME, t2.inventory AS TABLE02INVENTORY,
--(coalesce(table02.inventory,0)) AS TABLE02INVENTORY,
       SUM(t2.inventory) OVER (PARTITION BY t1.ITEMID order by t1.ITEMID  ) as SUMTABLE02INVENTORY,
       (t1.inventory - SUM(t2.inventory) OVER (PARTITION BY t1.ITEMID order by t1.ITEMID )) AS VARBYSUM 
FROM Table01 t1 LEFT JOIN
     Table02 t2 
     ON t1.ITEMID = t2.ITEMID AND
        t1.warehouse = t2.warehouse 
GROUP BY t1.ITEMID, t1.WAREHOUSE, t1.LOCATION, t1.INVENTORY, t2.ITEMID, 
         t2.WAREHOUSE, t2.LOCATION, t2.LOCATIONNAME, t2.inventory;

您可以将其扩展到要限制输出的任何其他列。

推荐阅读