Select Top 1 with ties with distinct on another field column

Question

我有一个这样的示例表记录：

AutoID  ReceiptNo  Outlet_Code  
-------------------------------- 
    1       SUN12        Store X     
    3       SUN12        Store X     
    4       ART123       Store y

我想在ReceiptNo上选择一个基于Outlet_Code和Distinct的平局的前1，这样在这种情况下，我的结果将是：

X商店/Y商店

因为我应该将2条Store X记录读取为1，因为它们具有相同的receiptNo。

这是我查询的一部分：

SELECT TOP 1 WITH TIES (Outlet_Code)                  
From [Trans] T                 
WHERE T.CardNo IN (Select [CardNo] From [Card]                
                         Where  [MemberID] = 
                         (Select [ID] From [Member]  Where [autoid] = @MemAutoID ))   
GROUP BY Outlet_Code     
ORDER BY Count(T.Outlet_Code) Desc 

Answer 1

Ewww。。。子查询。。。这对你有用吗？

SELECT DISTINCT T.Outlet_Code  
FROM [Trans] T, [Card] C, [Member] M
WHERE T.CardNo = C.CardNo
AND C.MemberID = M.ID
AND M.autoid = @MemAutoID
ORDER BY Count(T.Outlet_Code) Desc 

或者不那么懒惰的方式。。。

SELECT DISTINCT T.Outlet_Code  
FROM [Trans] AS T
JOIN  [Card] AS C ON (T.CardNo = C.CardNo)
JOIN [Member] AS M ON (C.MemberID = M.ID)
WHERE M.autoid = @MemAutoID
ORDER BY Count(T.Outlet_Code) Desc 

Answer 2

我不知道我是否正确理解你，但使用DISTINCT怎么样？

SELECT DISTINCT Outlet_Code FROM tableName

Answer 3

GROUP BY子句正确，但在ORDER BY子句中必须使用不同的列值ReceiptNo

SELECT TOP 1 WITH TIES Outlet_Code                  
FROM [Trans] T                 
WHERE T.CardNo IN (
                   SELECT c.[CardNo]
                   FROM [Card] c JOIN [Member] m ON c.[MemberID] = m.ID                
                   WHERE m.[autoid] = @MemAutoID                   
                   )
GROUP BY T.Outlet_Code     
ORDER BY COUNT(DISTINCT T.ReceiptNo) DESC

SQLFiddle