mysql按字段行数排序

mysql按字段行数排序

1、建表语句

create table `t_archives` (
    `id` varchar (96),
    `department_id` varchar (96)
); 
insert into `t_archives` (`id`, `department_id`) values('5b028f80a3105f31d46ce114','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b028fa500326e4f6a2f9646','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b028fc2a3105f31d46ce120','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b028ff6a3105f31d46ce12d','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b029012a3105f31d46ce13a','0000000028');
insert into `t_archives` (`id`, `department_id`) values('5b02903fa3105f31d46ce140','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b0290a1a3105f31d46ce149','0000000028');
insert into `t_archives` (`id`, `department_id`) values('5b0290a6a3105f31d46ce14f','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b0292b500326e4f6a2f975e','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b02947ea3105f31d46ce169','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b02950900326e4f6a2f97cc','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b0295ca00326e4f6a2f9811','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b0295dea3105f31d46ce17c','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b02962c00326e4f6a2f9829','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b0296aa00326e4f6a2f9847','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b0296f4a3105f31d46ce188','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b02976800326e4f6a2f988d','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b0297c9a3105f31d46ce194','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b0297e000326e4f6a2f98bf','0000000028');
insert into `t_archives` (`id`, `department_id`) values('5b029915a3105f31d46ce19a','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b02997d00326e4f6a2f9951','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b029da900326e4f6a2f9a52','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b029e1600326e4f6a2f9a6a','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b036bcea3105f31d46ce230','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b036ca6a3105f31d46ce23c','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b036ceca3105f31d46ce247','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b036cf900326e4f6a2fa511','0000000027');
insert into `t_archives` (`id`, `department_id`) values('5b036d1aa3105f31d46ce24d','0000000028');
insert into `t_archives` (`id`, `department_id`) values('5b036d2ca3105f31d46ce255','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b036d4000326e4f6a2fa526','0000000028');
insert into `t_archives` (`id`, `department_id`) values('5b036d9c00326e4f6a2fa530','1000002184');
insert into `t_archives` (`id`, `department_id`) values('5b036deca3105f31d46ce25c','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b036e41a3105f31d46ce262','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b036e62a3105f31d46ce268','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b036ed7a3105f31d46ce271','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b036eefa3105f31d46ce279','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b036f0800326e4f6a2fa554','0000000027');
insert into `t_archives` (`id`, `department_id`) values('5b036f3800326e4f6a2fa568','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5b036fbe00326e4f6a2fa57f','1000002184');
insert into `t_archives` (`id`, `department_id`) values('5b036fc7a3105f31d46ce282','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b037018a3105f31d46ce289','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b03702000326e4f6a2fa593','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b037ff700326e4f6a2fa7bf','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b062f83a310e8b66f0b77bb','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b062f8a00322dffdbac3151','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b062fcaa310e8b66f0b77c1','0000000033');
insert into `t_archives` (`id`, `department_id`) values('5b062fd100322dffdbac3175','0000000025');
insert into `t_archives` (`id`, `department_id`) values('5b062ff700322dffdbac3187','0000000026');
insert into `t_archives` (`id`, `department_id`) values('5b062ff900322dffdbac318e','0000000024');
insert into `t_archives` (`id`, `department_id`) values('5c662ffd84ae63c8781f7b04','0000000022');

如果建表语句报错，建议手写创建表的语句

2、查询sql

   SELECT
        c.id "id",
        c.department_id "departmentId" ,
        d.count
    FROM
        t_archives c 
   LEFT JOIN
        (
            SELECT
		COUNT(1) "count",
                b.department_id "departmentId",
                b.id 
            FROM
                t_archives b 
                   
            WHERE
                b.id IN(
                    '5c662ffd84ae63c8781f7b04','5b062ff900322dffdbac318e','5b062ff700322dffdbac3187','5b062fd100322dffdbac3175','5b062fcaa310e8b66f0b77c1','5b062f8a00322dffdbac3151','5b062f83a310e8b66f0b77bb','5b037ff700326e4f6a2fa7bf','5b03702000326e4f6a2fa593','5b037018a3105f31d46ce289','5b036fc7a3105f31d46ce282','5b036fbe00326e4f6a2fa57f','5b036f3800326e4f6a2fa568','5b036f0800326e4f6a2fa554','5b036eefa3105f31d46ce279','5b036ed7a3105f31d46ce271','5b036e62a3105f31d46ce268','5b036e41a3105f31d46ce262','5b036deca3105f31d46ce25c','5b036d9c00326e4f6a2fa530','5b036d4000326e4f6a2fa526','5b036d2ca3105f31d46ce255','5b036d1aa3105f31d46ce24d','5b036cf900326e4f6a2fa511','5b036ceca3105f31d46ce247','5b036ca6a3105f31d46ce23c','5b036bcea3105f31d46ce230','5b029e1600326e4f6a2f9a6a','5b029da900326e4f6a2f9a52','5b02997d00326e4f6a2f9951','5b029915a3105f31d46ce19a','5b0297e000326e4f6a2f98bf','5b0297c9a3105f31d46ce194','5b02976800326e4f6a2f988d','5b0296f4a3105f31d46ce188','5b0296aa00326e4f6a2f9847','5b02962c00326e4f6a2f9829','5b0295dea3105f31d46ce17c','5b0295ca00326e4f6a2f9811','5b02950900326e4f6a2f97cc','5b02947ea3105f31d46ce169','5b0292b500326e4f6a2f975e','5b0290a6a3105f31d46ce14f','5b0290a1a3105f31d46ce149','5b02903fa3105f31d46ce140','5b029012a3105f31d46ce13a','5b028ff6a3105f31d46ce12d','5b028fc2a3105f31d46ce120','5b028fa500326e4f6a2f9646','5b028f80a3105f31d46ce114'
                ) 
            GROUP BY
                b.department_id
        ) d 
            ON c.department_id=d.departmentId
    WHERE
        c.id IN (
            '5c662ffd84ae63c8781f7b04','5b062ff900322dffdbac318e','5b062ff700322dffdbac3187','5b062fd100322dffdbac3175','5b062fcaa310e8b66f0b77c1','5b062f8a00322dffdbac3151','5b062f83a310e8b66f0b77bb','5b037ff700326e4f6a2fa7bf','5b03702000326e4f6a2fa593','5b037018a3105f31d46ce289','5b036fc7a3105f31d46ce282','5b036fbe00326e4f6a2fa57f','5b036f3800326e4f6a2fa568','5b036f0800326e4f6a2fa554','5b036eefa3105f31d46ce279','5b036ed7a3105f31d46ce271','5b036e62a3105f31d46ce268','5b036e41a3105f31d46ce262','5b036deca3105f31d46ce25c','5b036d9c00326e4f6a2fa530','5b036d4000326e4f6a2fa526','5b036d2ca3105f31d46ce255','5b036d1aa3105f31d46ce24d','5b036cf900326e4f6a2fa511','5b036ceca3105f31d46ce247','5b036ca6a3105f31d46ce23c','5b036bcea3105f31d46ce230','5b029e1600326e4f6a2f9a6a','5b029da900326e4f6a2f9a52','5b02997d00326e4f6a2f9951','5b029915a3105f31d46ce19a','5b0297e000326e4f6a2f98bf','5b0297c9a3105f31d46ce194','5b02976800326e4f6a2f988d','5b0296f4a3105f31d46ce188','5b0296aa00326e4f6a2f9847','5b02962c00326e4f6a2f9829','5b0295dea3105f31d46ce17c','5b0295ca00326e4f6a2f9811','5b02950900326e4f6a2f97cc','5b02947ea3105f31d46ce169','5b0292b500326e4f6a2f975e','5b0290a6a3105f31d46ce14f','5b0290a1a3105f31d46ce149','5b02903fa3105f31d46ce140','5b029012a3105f31d46ce13a','5b028ff6a3105f31d46ce12d','5b028fc2a3105f31d46ce120','5b028fa500326e4f6a2f9646','5b028f80a3105f31d46ce114'
        )
    ORDER BY
        d.count DESC,d.departmentId DESC;