我的查询正在返回副本

Question

我编写了一个SQL查询，用于筛选多个条件，并使用distinct只查找唯一的记录。

具体来说，我只需要AccountID字段是唯一的，每个AccountID都有多个AddressClientIDs。

该查询工作正常，但正在生成一些重复的查询。

进一步的注意事项是：

1. 每个AccountID都有多个反式
2. 对于一个Y和N，可以有跨记录。

我只想返回AccountIDs，它的状态不是指定的，所以我不使用它，因为我不想要这两个状态。

我只想为AccountID列找到唯一的值。

如果有人能帮助完善下面的查询，我们将不胜感激。

SELECT AFS_Account.AddressClientID
    ,afs_transunit.AccountID
    ,SUM(afs_transunit.Units)
FROM AFS_TransUnit
    ,AFS_Account
WHERE afs_transunit.AccountID IN (
        -- Gets accounts which only have non post statuses
        SELECT DISTINCT accountid
        FROM afs_trans
        WHERE accountid NOT IN (
                SELECT accountid
                FROM afs_trans
                WHERE STATUS IN (
                        'POSTPEND'
                        ,'POSTWAIT'
                        )
                )
            -- This gets the unique accountIDs which only have transactions with Y status,
            -- and removes any which have both Y and N.
            AND AccountID IN (
                SELECT DISTINCT accountid
                FROM afs_trans
                WHERE IsAllocated = 'Y'
                    AND accountid NOT IN (
                        SELECT DISTINCT AccountID
                        FROM afs_trans
                        WHERE IsAllocated = 'N'
                        )
                )
        )
    AND AFS_TransUnit.AccountID = AFS_Account.AccountID
GROUP BY afs_transunit.AccountID
    ,AFS_Account.AddressClientID
HAVING SUM(afs_transunit.Units) > 100

谢谢。

Answer 1

由于您确认了AccountID列上的两个表之间存在一对多的关系，所以可以使用AccountID的Max值来获得不同的值：

SELECT afa.AddressClientID
    ,MAX(aft.AccountID)
    ,SUM(aft.Units)
FROM AFS_TransUnit aft
INNER JOIN AFS_Account afa ON aft.AccountID = afa.AccountID
GROUP BY afa.AddressClientID
HAVING SUM(aft.Units) > 100
    AND MAX(aft.AccountID) IN (
        -- Gets accounts which only have non post statuses
        -- This gets the unique accountIDs which only have transactions with Y status,
         -- and removes any which have both Y and N.
        SELECT DISTINCT accountid
        FROM afs_trans a
        WHERE [STATUS] NOT IN ('POSTPEND','POSTWAIT')
            AND a.accountid IN (
                SELECT t.accountid
                FROM (
                    SELECT accountid
                        ,max(isallocated) AS maxvalue
                        ,min(isallocated) AS minvalue
                    FROM afs_trans
                    GROUP BY accountid
                    ) t
                WHERE t.maxvalue = 'Y'
                    AND t.minvalue = 'Y'
                )
        )

Answer 2

SELECT AFS_Account.AddressClientID
    ,afs_transunit.AccountID
    ,SUM(afs_transunit.Units)
FROM AFS_TransUnit
INNER JOIN AFS_Account ON AFS_TransUnit.AccountID = AFS_Account.AccountID
INNER JOIN afs_trans ON afs_trans.acccountid = afs_transunit.accountid
WHERE afs_trans.STATUS NOT IN ('POSTPEND','POSTWAIT')
    -- AND afs_trans.isallocated = 'Y'
GROUP BY afs_transunit.AccountID
    ,AFS_Account.AddressClientID
HAVING SUM(afs_transunit.Units) > 100
and max(afs_trans.isallocated) = 'Y' 
and min(afs_trans.isallocated) = 'Y'

使用ANSI SQL联接语法修改查询。在加入这些表时，只需指定条件，而不必使用所拥有的子查询。