删除给定键总和为零的行

2023-11-26

我有一个查询，该查询将导致在我们的 SSRS 2008 R2 服务器上创建客户账单。 SQL Server实例也是2008 R2。查询很大，出于安全原因等原因我不想发布整个内容。

我需要对下面的示例数据执行的操作是从结果集中删除带有 73.19 和 -73.19 的两行。因此，如果两行在 LineBalance 列中具有相同的绝对值且它们的总和为 0 并且如果它们在 REF1 列中具有相同的值，则应从结果集中删除它们。 REF1 = 14598 且行余额为 281.47 的行仍应返回结果集中，并且不应返回下面 REF1 = 14598 的其他两行。

这样做的目的是向客户“隐藏”会计错误及其更正。我所说的“隐藏”是指，不要将其显示在他们通过邮件收到的账单上。这里发生的情况是，客户应该被计费为 281.47，但错误地被计费为 73.19。所以，我们的 AR 部门。将 73.19 返回到他们的帐户，并向他们收取正确的金额 281.47。正如您所看到的，它们都具有相同的 REF1 值。

An Example Of My Data

我会添加一个包含显式标志的字段，告诉您某个费用是错误/错误的逆转，然后过滤掉这些行就很简单了。即时执行可能会使您的报告变得相当慢。

但是，为了解决给定的问题，我们可以这样做。该解决方案假设SysInvNum是独特的。

创建包含示例数据的表

DECLARE @T TABLE (SysInvNum int, REF1 int, LineBalance money);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344299, 14602, 558.83);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344298, 14598, 281.47);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344297, 14602, -95.98);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344296, 14598, -73.19);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3341758, 14598, 73.19);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (11, 100, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (12, 100, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (13, 100, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (21, 200, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (22, 200, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (23, 200, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (31, 300, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (32, 300, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (33, 300, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (34, 300, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (41, 400, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (42, 400, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (43, 400, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (44, 400, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (45, 400, 50.00);

我又添加了一些有多个错误的案例。

行数和计数

SELECT
    SysInvNum
    , REF1
    , LineBalance
    , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
    , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
FROM @T AS TT

这是结果集：

SysInvNum    REF1    LineBalance    rn    cc1
11           100      50.00         1     3
12           100     -50.00         1     3
13           100      50.00         2     3
21           200     -50.00         1     3
23           200      50.00         1     3
22           200     -50.00         2     3
31           300     -50.00         1     4
32           300      50.00         1     4
33           300     -50.00         2     4
34           300      50.00         2     4
41           400      50.00         1     5
42           400     -50.00         1     5
43           400      50.00         2     5
44           400     -50.00         2     5
45           400      50.00         3     5
3341758      14598    73.19         1     2
3344296      14598   -73.19         1     2
3344298      14598   281.47         1     1
3344297      14602   -95.98         1     1
3344299      14602   558.83         1     1

您可以看到那些有错误的行的计数 > 1。此外，成对的错误具有相同的行号。因此，我们需要删除/隐藏那些 count > 1 的行以及那些具有两个相同行号的行。

确定要删除的行

WITH
CTE_rn
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
        , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
    FROM @T AS TT
)
, CTE_ToRemove
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , COUNT(*) OVER(PARTITION BY REF1, rn) AS cc2
    FROM CTE_rn
    WHERE CTE_rn.cc1 > 1
)
SELECT *
FROM CTE_ToRemove
WHERE CTE_ToRemove.cc2 = 2

这是另一个中间结果：

SysInvNum    REF1    LineBalance    cc2
12           100     -50.00         2
11           100      50.00         2
21           200     -50.00         2
23           200      50.00         2
32           300      50.00         2
31           300     -50.00         2
33           300     -50.00         2
34           300      50.00         2
42           400     -50.00         2
41           400      50.00         2
43           400      50.00         2
44           400     -50.00         2
3344296      14598   -73.19         2
3341758      14598    73.19         2

现在，我们把所有这些放在一起。

最终查询

WITH
CTE_rn
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
        , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
    FROM @T AS TT
)
, CTE_ToRemove
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , COUNT(*) OVER(PARTITION BY REF1, rn) AS cc2
    FROM CTE_rn
    WHERE CTE_rn.cc1 > 1
)
SELECT *
FROM @T AS TT
WHERE
    TT.SysInvNum NOT IN 
    (
        SELECT CTE_ToRemove.SysInvNum
        FROM CTE_ToRemove
        WHERE CTE_ToRemove.cc2 = 2
    )
ORDER BY SysInvNum;

Result:

SysInvNum    REF1    LineBalance
13           100       50.00
22           200      -50.00
45           400       50.00
3344297      14602    -95.98
3344298      14598    281.47
3344299      14602    558.83

请注意，最终结果没有任何 REF = 300 的行，因为有两个已更正的错误，完全相互平衡。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

sql

sqlserver