按ID分列每列中的ID

I have a table like this:

我有这样一张桌子:

id | col1 | col2 | col3 |
-------------------------
1  | ab   |  ab  |      |
2  | bc   |  ab  |  cd  |
3  | bc   |  cd  |  cd  |

And i want to produce the count of each of the names in each column like this:

我想生成每列中每个名称的计数,如下所示:

name | col1 | col2 | col3 |
-------------------------
 ab  | 1    |  2   |   0  |
 bc  | 2    |  0   |   0  |
 cd  | 0    |  1   |   2  |

Note that this is just an example, in reality there are 1000s of names, the solution will have to specify the columns, but not the names.

请注意,这只是一个示例,实际上有1000个名称,解决方案必须指定列,而不是名称。

This is probably very simple, and I tried to search for it but couldn't find anything quite right, likely because i didn't know the right term.

这可能很简单,我试图搜索它但找不到任何正确的东西,可能是因为我不知道正确的术语。

I was using something like this to count the total occurrences of each name, but I can't figure out how to split it back out by column: Selecting distinct records from multiple column of a table with their count

我使用这样的东西来计算每个名称的总出现次数,但我无法弄清楚如何按列拆分它:从表的多列中选择不同的记录及其计数

This is using MYSQL.

这是使用MYSQL。

3 个解决方案

#1

select name, sum(cnt1), sum(cnt2), sum(cnt3)
from
(
select col1 as name, count(*) as cnt1 , null as cnt2 , null as cnt3 from t group by col1
union all
select col2, null, count(*), null from t group by col2
union all
select col3, null, null, count(*) from t group by col3
) as dt

#2

perhaps this would work

也许这会奏效

 select colcounts.col, sum(colcounts.cnt) from
 (
  (select col1 as col, count(*) as cnt from TableLikeThis group by col1)
  union 
  (select col2 as col, count(*) as cnt from TableLikeThis group by col2)
  union 
  (select col3 as col, count(*) as cnt from TableLikeThis group by col3) 
 ) as colcounts
 group by col

Sql Fiddle Here

Sql在这里小提琴

#3

Hope this will help :

希望这会有所帮助:

select name ,sum(s1), sum(s2), sum(s3)
    from
    (
    select cnt1 as name , count(cnt1) as s1, count(if(cnt2=cnt1,1,0)) as s2, count(if(cnt3=cnt1,1,0)) as s3
        from your_table group by cnt1
    union all 
    select cnt2 as name , count(if(cnt1=cnt2,1,0)) as s1, count(cnt2) as s2, count(if(cnt3=cnt2,1,0)) as s3
        from your_table group by cnt2
    union all 
    select cnt3 as name , count(if(cnt1=cnt3,1,0)) as s1, count(if(cnt2=cnt3,1,0)) as s2, count(cnt3) as s3
        from your_table group by cnt3
    )
     group by name

#1

select name, sum(cnt1), sum(cnt2), sum(cnt3)
from
(
select col1 as name, count(*) as cnt1 , null as cnt2 , null as cnt3 from t group by col1
union all
select col2, null, count(*), null from t group by col2
union all
select col3, null, null, count(*) from t group by col3
) as dt

#2