根据糖果编号显示正确的行

Question

>目标：
如果一个人有两个糖果号码，数字 1 应始终首先显示。无需显示 2 号糖果。
如果一个人没有数字 1，它应该显示数字 2。

Display all data
(int)(int)  (nvarchar) (int)
Id  fId     Name     Candy Number
---------------------------------
1    12     Kimn                1
2    12     Kimn                2
3    19     Lisa                1
4    15     John                2
5    16     Maria               2
6    16     Maria               1
7    17     Mao                 2

Requested result:
Id  fId     Name     Candy Number
---------------------------------
1    12     Kimn                1
3    19     Lisa                1
4    15     John                2
6    16     Maria               1
7    17     Mao                 2

问题：
对我来说，显示它效果不佳。尝试使用大小写并以 where 语句结尾，但代码不符合目的。

知道吗？

select *
from
    table
where
    candynumber = 
        CASE WHEN b.MatchType = 1 
                THEN 1
            ELSE 2
        END

谢谢！

Answer 1

这可以使用row_number()窗口函数：

select Id, fId, Name, Candy_Number from (
    select your_table.*, row_number() over(partition by fId order by Candy_Number) as rn from your_table
) t 
where rn = 1
order by id

这给出了每fId一行，Candy_Number较低。

Answer 2

你可以试试这个：

SELECT          candyWrapper.ID,
                candyWrapper.FID,
                outerHardCandy.Name,
                outerHardCandy.Number
FROM            (SELECT     innerSoftCandy.Name,
                            CASE
                                WHEN (SUM(innerSoftCandy.Number) = 3) OR (SUM(innerSoftCandy.Number) = 1) THEN 1
                                WHEN (SUM(innerSoftCandy.Number) = 2) THEN 2
                            END AS Number
                FROM        Candy innerSoftCandy
                GROUP BY    innerSoftCandy.Name
                ) outerHardCandy
INNER JOIN      Candy candyWrapper ON (outerHardCandy.Name = candyWrapper.Name AND outerHardCandy.Number = candyWrapper.Number)
ORDER BY        candyWrapper.ID

你可以在这里看到这个 -> http://rextester.com/BBD89608