在同一列中使用分组依据来计算相同的ID

本文介绍了在同一列中使用分组依据来计算相同的ID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这样的桌子 kondisi

+------------+----------------+ | id_kondisi | id_sub_kondisi | +------------+----------------+ | 01 | 0102 | | 03 | 0302 | | 01 | 0101 | | 01 | 0102 | | 01 | 0101 | | 03 | 0301 | | 03 | 0303 | | 02 | 0202 | | 01 | 0102 | | 03 | 0301 | | 01 | 0101 | | 02 | 0203 | | 03 | 0302 | | 02 | 0202 | | 02 | 0201 | | 02 | 0202 | +------------+----------------+ 16 rows in set (0.00 sec)

我想要一张看起来像这样的表的结果

I want to a result of the table that looks like this

+----------------+-------------+ | kondisi_tot | coun_tot | +----------------+-------------+ | 01 | 6 | | 0101 | 3 | | 0102 | 3 | | 02 | 5 | | 0201 | 1 | | 0202 | 3 | | 0203 | 1 | | 03 | 5 | | 0301 | 2 | | 0302 | 2 | | 0303 | 1 | +----------------+-------------+

所以我需要计算已经循环的数据ID.就像上面的结果 我知道我必须使用group by，但是如何将另一列变成一列呢?

SO i need to count the data id that have been looping. Just like the above result i know i have to use group by but how do i make the other column into one column ?

PS:我的id_kondisi和id_sub_kondisi是char类型，而不是int类型

推荐答案

类似的东西应该可以工作.请注意，UNION ALL用于确保对所有值都进行计数.然后将ID值强制转换为CHAR以使排序顺序起作用.

Something like this should work. Note that UNION ALL is used to make sure all values are counted; the ID value is then casted to CHAR to make the sort order work.

SQL小提琴

MySQL 5.6模式设置:

CREATE TABLE kondisi (`id_kondisi` int, `id_sub_kondisi` int) ; INSERT INTO kondisi (`id_kondisi`, `id_sub_kondisi`) VALUES (01, 0102), (03, 0302), (01, 0101), (01, 0102), (01, 0101), (03, 0301), (03, 0303), (02, 0202), (01, 0102), (03, 0301), (01, 0101), (02, 0203), (03, 0302), (02, 0202), (02, 0201), (02, 0202) ;

查询1 :

select id, count(id) from (select id_kondisi as id from kondisi union all select id_sub_kondisi from kondisi) merged_table group by id order by cast(id as char)

结果 :

Results:

| id | count(id) | |-----|-----------| | 1 | 6 | | 101 | 3 | | 102 | 3 | | 2 | 5 | | 201 | 1 | | 202 | 3 | | 203 | 1 | | 3 | 5 | | 301 | 2 | | 302 | 2 | | 303 | 1 |