SQL 服务器 : most frequent value in each row

Question

如何在 SQL Server 的每一行中找到最频繁出现的值？

例子:

1 a d a a c a b  -->    a
2 b a c b b b d  -->    b
3 h a h h b c d  -->    h    
4 d d c h g p m  -->    d
5 e e g h d e h  -->    e

在第一行中，'a' 是出现频率最高的值，依此类推。

Answer 1

考虑到这些值位于单独的列中，使用 UNPIVOT 查询，解决方案看起来像......

测试数据

Declare @T table (ID INT , Col1 varchar(1) , Col2 varchar(1) , Col3 varchar(1)
             , Col4 varchar(1) , Col5 varchar(1) , Col6 varchar(1) , Col7 varchar(1))
Insert Into @T values
('1','a','d','a','a','c','a','b'),
('2','b','a','c','b','b','b','d'),
('3','h','a','h','h','b','c','d'),
('4','d','d','c','h','g','p','m'),
('5','e','e','g','h','d','e','h');

查询

WITH X AS (
Select ID , Val,  COUNT(*)total
       ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COUNT(*) DESC) rn
from @T 
 UNPIVOT (Val FOR N IN (Col1,Col2,Col3,Col4,Col5,Col6,Col7))up
GROUP BY ID , Val 
)
Select t.* ,  Val 
FROM X 
INNER JOIN @T t ON x.ID = t.ID 
WHERE rn = 1

结果集

+----+------+------+------+------+------+------+------+-----+
| ID | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 | Val |
+----+------+------+------+------+------+------+------+-----+
|  1 | a    | d    | a    | a    | c    | a    | b    | a   |
|  2 | b    | a    | c    | b    | b    | b    | d    | b   |
|  3 | h    | a    | h    | h    | b    | c    | d    | h   |
|  4 | d    | d    | c    | h    | g    | p    | m    | d   |
|  5 | e    | e    | g    | h    | d    | e    | h    | e   |
+----+------+------+------+------+------+------+------+-----+