sql - 如何对多个表进行分组和排序

Question

如果要从多个表中获取列，如何按列分组并按频率排序？例如，我有3个表：Table_A，Table_B，Table_C。他们每个人都有一列相同的数据。
例如Table_A

col
------
aaa
bbb
aaa
ccc

例如Table_B

aaa
ccc
ddd
bbb

同样，Table_C的数据相似。我想得到以下结果：

aaa 3
bbb 2
ccc 2
ddd 1

目前，我有这个

select col, count(*) from Table_A UNION select col, count(*) from Table_B UNION select col, count(*) from Table_C group by col order by count(*) desc

但这并没有给我想要的结果。谢谢

Answer 1

select      col
           ,count(*) as cnt

from        (         select col from Table_A
            union all select col from Table_B
            union all select col from Table_C
            ) t

group by    col

order by    cnt desc
           ,col
;

要么

select      col
           ,sum(cnt) as sum_cnt

from        (         select col,count(*) as cnt from Table_A group by col
            union all select col,count(*)        from Table_B group by col
            union all select col,count(*)        from Table_C group by col
            ) 

group by    col

order by    sum_cnt desc
           ,col
;