根据值在3个表中的出现次数选择最上面的第n行

Question

我有三个表，比如A、B和C。每个表都有不同名称的列，比如D1、D2和D3。在这些列中，我的值在1到26之间。如何计算这些值的出现次数并按该次数对其进行排序？示例：

TableA.D1
1
2
1
1
3
TableB.D2
2
1
1
1
2
3
TableC.D3
2
1
3

因此，第三常见值的输出如下所示：

3 -- number 3 appeared only 3 times

同样，第二常见值的输出为：

2 -- number 2 appeared 4 times

第1个最常见值的输出：

1 -- number 1 appeared 7 times

Answer 1

您可能想要：

select top (3) d1
from ((select d1 from tablea ta) union all
(select d2 from tableb tb) union all
(select d3 from tablec tc)
) t
group by d1
order by count(*) desc;

Answer 2

SELECT DQ3.X, DQ3.CNT
(
SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
(SELECT DS.X,COUNT(DS.X) CNT FROM
(select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
GROUP BY DS.X
) DQ2
) DQ3 WHERE DQ3.RN = 3   --the third in the order of commonness - note that 'ties' can be handled differently

Answer 3

SQL脚本的一个特点是：它们很难阅读。我非常喜欢让东西尽可能地可读。所以我推荐这样的东西：

declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
from
(
select d1 as entry from tablea UNION ALL
select d2 as entry from tableb UNION ALL
select d3 as entry from tablec UNION ALL
) as allTablesCombinedSubquery
order by count(*)
select TOP 1 entry
from @topThree
order by cnt desc

它可读性很强，并且没有使用任何难以理解的概念。