横向连接似乎是正确的方法。作为工作的一部分，我将进行汇总：

select t.*, (case when c.num_duplicates > 0 then 'Yes' else 'No' end) as lastDigit,
       coalesce(c.num_duplicates, 0)
from t cross apply
     (select sum(cnt) as num_duplicates
      from (select f % 10 as digit, count(*) as cnt
            from (values (t.f1), (t.f2), (t.f3), (t.f4), (t.f5), (t.f6)) v(f)
            group by f % 10
            having count(*) > 1
           ) c
     ) c;

这是一把小提琴。

这是个有趣的问题。你可以通过横向连接来完成。其思想是将数字列拆分为行，按最后一位进行聚合，并只保留最常出现的数字。然后只需要添加一点条件逻辑：

select *
from mytable t
cross apply (
    select top (1) 
        case when count(*) = 1 then 'No' else 'Yes'    end LastDigit,
        case when count(*) = 1 then 0    else count(*) end CountLastDigit
    from (values(f1), (f2), (f3), (f4), (f5), (f6)) x(f)
    group by f % 10
    order by CountLastDigit desc
) z

db小提琴演示：

Id | F1 | F2 | F3 | F4 | F5 | F6 | LastDigit | CountLastDigit
-: | -: | -: | -: | -: | -: | -: | :-------- | -------------:
 1 |  5 | 11 | 15 | 21 | 30 | 36 | Yes       |              2
 2 |  8 | 10 | 21 | 25 | 32 | 39 | No        |              0
 3 | 10 | 18 | 23 | 27 | 28 | 30 | Yes       |              2

如果要对顶部关系的计数求和，则需要在子查询中再进行一级聚合：

select *
from mytable t
cross apply (
    select 
        max(LastDigit) LastDigit,
        sum(CountLastDigit) CountLastDigit
    from (
        select top (1) with ties
            case when count(*) = 1 then 'No' else 'Yes'    end LastDigit,
            case when count(*) = 1 then 0    else count(*) end CountLastDigit
        from (values(f1), (f2), (f3), (f4), (f5), (f6)) x(f)
        group by f % 10
        order by CountLastDigit desc
    ) z
) z

请尝试以下操作：

;WITH CTE AS
(
    SELECT ID, COL, VAL
    FROM
    (
        SELECT Id, F1%10 F1, F2%10 F2, F3%10 F3, F4%10 F4, F5%10 F5, F6%10 F6
        FROM @TAB
    )T
    UNPIVOT 
    (
        VAL FOR COL IN ( F1, F2, F3, F4, F5, F6 )
    )UPVT
)
, CTE2 AS
(
    SELECT ID, VAL LastDigit, COUNT(DISTINCT COL) VAL_COUNT 
    FROM CTE
    GROUP BY ID, VAL
)
SELECT T.Id, F1, F2, F3, F4, F5, F6
, CASE WHEN VAL_COUNT > 1 THEN 'YES' ELSE 'NO' END LastDigit
, CASE WHEN VAL_COUNT > 1 THEN SUM(VAL_COUNT) ELSE 0 END CountLastDigit 
FROM @tab T
LEFT JOIN CTE2 ON T.Id = CTE2.Id AND VAL_COUNT > 1
GROUP BY T.Id, F1, F2, F3, F4, F5, F6, VAL_COUNT
ORDER BY Id

请在这里找到小提琴。