如何计算相同的数字并从每列返回多路复用计数数字值

Question

我有表：

+-----+-----+-----+-----+
| ID  | C1  |  C2 | C3  |
+-----+-----+-----+-----+
|  1  |  5  |  8  |  3  |
|  2  |  7  |  4  |  7  |
|  3  |  5  |  8  |  7  |
|  4  |  8  |  2  |  7  |
+-----+-----+-----+-----+

我想从每列返回一个计数更多的数字。 例如，在此方案中：

+----+----+----+
| C1 | C2 | C3 |
+----+----+----+
| 5  | 8  | 7  |
+----+----+----+

最好的方法是什么？

我已经写了这个查询，但它仅适用于一列

SELECT TOP 1 
    C1, 
    count(*) as Col1
FROM [TableName] 
GROUP BY C1
ORDER BY Col1 DESC

Answer 1

试试这个：

创建示例数据

use tempdb
create table temp(
    id int,
    c1 int,
    c2 int,
    c3 int
)
insert into temp
select 1, 5, 8, 3 union all
select 2, 7, 4, 7 union all
select 3, 5, 8, 7 union all
select 4, 8, 2, 7

溶液

;with cte as(
    select c1 as val, 'c1' as col from temp union all
    select c2 as val, 'c2' as col from temp union all
    select c3 as val, 'c3' as col from temp
)
select
    max(case when col = 'c1' then val end) as c1,
    max(case when col = 'c2' then val end) as c2,
    max(case when col = 'c3' then val end) as c3
from (
    select
        col,
        val
    from cte
    group by col, val
    having count(*) > 1
)t

删除示例数据

drop table temp

Answer 2

这是一个使用多个 CTE 的解决方案，每列一个。

;with cte1
as
(select row_number() over (  order by count(*) desc) as rn,
       c1
from Table1
GROUP BY C1
)
,cte2
as
(select row_number() over (  order by count(*) desc) as rn,
       c2
from Table1
GROUP BY C2
)
,cte3
as
(select row_number() over (  order by count(*) desc) as rn,
       c3
from Table1
GROUP BY C3
)
select cte1.c1 as c1,
       cte2.c2 as c2,
       cte3.c3 as c3
from cte1,cte2, cte3
where cte1.rn = 1
and cte2.rn =1
and cte3.rn =1