选择可能出现在多列中的结果的频率(SQL) [英] Selecting the frequency of a result that could appear in multiple columns (SQL)

问题描述

我有一个表，其中列出了分布在五个不同列中的名称.我正在尝试获得6个最常见的不同名称.每个名称只会在每个记录中出现一次.这五列是name_1，name_2 ... name_5.只是出于名称的缘故，请将该表称为"mytable".

I have a table with a list of names spread across five different columns. I'm trying to get the 6 most frequent distinct names. Each name will only appear in each record once. The five columns are name_1, name_2...name_5. And just for names sake call the table 'mytable'.

任何帮助将不胜感激.

推荐答案

这里是一种方法:

SELECT name, COUNT(1)
  FROM (           SELECT name_1 AS name FROM mytable
         UNION ALL SELECT name_2 AS name FROM mytable
         UNION ALL SELECT name_3 AS name FROM mytable
         UNION ALL SELECT name_4 AS name FROM mytable
         UNION ALL SELECT name_5 AS name FROM mytable
       ) AS myunion
 GROUP BY name
 ORDER BY COUNT(1) DESC LIMIT 6
;

表中有多少行?

这篇关于选择可能出现在多列中的结果的频率(SQL)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！