SQL Server多表关联查询：筛选含有效FileID的账户数据

Got it, let's tackle that duplicate account issue you're facing. The problem with your Codeblock2 is that joining directly to tblItems creates duplicate rows—since a single account might have multiple fileID > 0 entries in tblItems, each of those entries will pair with the account's order/account data, causing repeats.

Here's the cleanest fix, building on your original (working) Codeblock1 to add the tblItems condition without introducing duplicates:

SELECT *, CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox 
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY dateAdded DESC) AS [RowNo], 
        COUNT(*) OVER () cnt, 
        tblAccounts.skypeUserName, 
        tblAccounts.contactName, 
        tblorders.friendlyOrderID 
    FROM tblOrders 
    INNER JOIN tblAccounts ON tblOrders.accountID = tblAccounts.accountID 
    WHERE 
        bootSaleDate = @bootSaleDate 
        AND orderStatus = 'Completed'
        -- Add the tblItems existence check here (no join needed!)
        AND EXISTS (
            SELECT 1 
            FROM tblItems 
            WHERE tblItems.AccountID = tblAccounts.AccountID 
            AND tblItems.FileID > 0
        )
) t 
WHERE RowNo BETWEEN (@page * 8) - 7 AND (@page * 8)

Why this works: #

• The EXISTS clause only checks if there's at least one matching fileID > 0 entry for the account—it doesn't pull in those tblItems rows, so your original row count and uniqueness stay intact.
• This is also efficient, especially if you have an index on tblItems(AccountID, FileID) (which you should consider adding if you don't already have it) to speed up the existence check.

If you prefer using an IN clause instead (which works similarly), here's that version:

SELECT *, CASE WHEN RowNo < cnt THEN 'N' ELSE 'Y' END AS lastbox 
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY dateAdded DESC) AS [RowNo], 
        COUNT(*) OVER () cnt, 
        tblAccounts.skypeUserName, 
        tblAccounts.contactName, 
        tblorders.friendlyOrderID 
    FROM tblOrders 
    INNER JOIN tblAccounts ON tblOrders.accountID = tblAccounts.accountID 
    WHERE 
        bootSaleDate = @bootSaleDate 
        AND orderStatus = 'Completed'
        AND tblAccounts.AccountID IN (
            SELECT DISTINCT AccountID 
            FROM tblItems 
            WHERE FileID > 0
        )
) t 
WHERE RowNo BETWEEN (@page * 8) - 7 AND (@page * 8)

Either approach will keep your account results unique while enforcing the requirement that only accounts with at least one valid fileID are returned.

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