主页 / dba / 问题 / 325841
Accepted
sql-noob
Asked: 2023-04-11 06:15:53 +0800 CST

为什么我的行重复并错误地显示总和？

  • 772

我有这些表：

Payments

vendor   | VendorLocation | VendorName   | CheckDate | Amount | TransactionID | InvoiceID
---------------------------------------------------------------------
777      |                | Sony        | 5/1/2020   | 1.50   | 345           | ABC555
777      |                | Sony        | 5/1/2020   | 1.20   | 444           | ABC555
777      |  103           | Sony Music  | 5/1/2020   | 8.50   | 888           | XYZ888
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 789           | XYZ999
777      |  105           | Sony VR     | 5/1/2020   | 2.50   | 833           | XYZ111

Vendor Location

vendor   | VendorLocation | LocationType |Address 1   | State | City
---------------------------------------------------------------------
777      |  103           | X            |123 Ave      | CA    | Rivertown
777      |  105           | Z            |666 Ave      | CA    | Northtown
777      |  106           | Z            |888 Ave      | CA    | Southtown

Vendor Address

vendor   | VendorLocation | Address 1  | State | City
---------------------------------------------------------------------
777      |                | 1 Main St  | CA    | Rivertown
777      |  103           | 123 Ave    | CA    | Rivertown
777      |  105           | 666 Ave    | CA    | Northtown

我想要的输出是：

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $5.00   | 666 Ave

但是，我得到的结果低于重复且不正确的 totalSpend 金额：

Vendor Spending

vendor   | VendorLocation | LocationType  | VendorName | TotalTransactions|TotalSpend| Address 1
---------------------------------------------------------------------
777      |                | Z             | Sony       |  2                | $2.70   | 1 Main St
777      |  105           | Z             | Sony VR    |  2                | $7.70   | 1 Main St


This is query I have:

select distinct
ap.vendor Vendor_ID
,vl.VendorLocation
,vl.LocationType
,ap.VendorName
,count(ap.transactionID) as TotalTransactions
,sum( ap.amount) as TotalSpend
,ad.Address1
from payments ap
join  VendorLocation vl
on ( (vl.vendor=ap.vendor and vl.VendorLocation=ap.VendorLocation)
or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and vl.LocationType in ('Z')
) join VENDORADDRESS ad
on (ad.VENDOR=ap.vendor AND ad.VendorLocation=ap.VendorLocation)
where
vl.LocationType in ('Z') and
ap.CheckDate>='4/1/2022'
group by
vl.vendor
,vl.LOCATION_CODE
,vl.LocationType
,ap.VendorName
,ad.Address1
sql-server-2016

1 个回答

  1. Best Answer

    您有一些相当非规范化的表。在多个表（例如Address 1）中重复相同的数据点是多余的、低效的，最重要的是会导致数据完整性差。我建议修复您的表格设计。

    除此之外，您的查询可以像这样简化：

    ;WITH _VendorTransactionTotals AS
(
    SELECT
        vendor, 
        VendorLocation, 
        VendorName,
        COUNT(1) AS TotalTransactions,
        SUM(Amount) AS TotalSpend
    FROM Payments
    WHERE CheckDate >= '4/1/2022'
    GROUP BY vendor, VendorLocation, VendorName
)

SELECT
    VTT.vendor, 
    VTT.VendorLocation,
    VL.LocationType,
    VTT.VendorName,
    VTT.TotalTransactions,
    VTT.TotalSpend,
    VL.[Address 1]
FROM _VendorTransactionTotals AS VTT
INNER JOIN [Vendor Location] AS VL
    ON VTT.vendor = VL.vendor
    AND VTT.VendorLocation = VL.VendorLocation
WHERE VL.LocationType = 'Z'

    通过首先使用 CTE 聚合您的Payments表，这会在加入表之前对其进行重复数据删除Vendor Location。这也使查询更具可读性并简化了代码。

    请注意，对于其中没有奇数字符（例如空格）的表和列，您还应该使用标准名称。

    顺便说一句，您的原始查询的问题很可能出在这里：

    or (vl.vendor=ap.vendor and vl.VendorLocation='')
or (vl.vendor=ap.vendor and ap.VendorLocation='')
and

    这就是说，Vendor Location表中任何带有空白的 VendorLocation行都可以连接到表中所有相同的行，反之亦然，表中任何带有空白的行都可以连接到表中所有相同的行。vendorPaymentsPayments VendorLocationVendorVendor Location

    此外，不清楚表格Vendor Address与Vendor Location表格的关系，尤其是空白地址。你怎么知道1 Main Strelates to LocationType = 'Z'？澄清这一点可能有助于确定您的目标是否可行以及如何最好地实现它。

    • 1

相关问题