现有一张表,此表为设备表,pid为设备的父级设备id,分别统计各设备有多少个子级设备。
第一种解决方法是一条一条输出:
select pid as id,count(*) as totalcount from project_equipments_type_grouping where pid=1;
select pid as id,count(*) as totalcount from project_equipments_type_grouping where pid=2;
select pid as id,count(*) as totalcount from project_equipments_type_grouping where pid=3;
即通过where语句分别统计不同属性的数量。
但显然这样写并不方便,能不能通过一条语句搞定了?
我又写了第二种解法:
select
sum(pid = 1) as 'id=1',
sum(pid = 2) as 'id=2',
sum(pid = 3) as 'id=3'
from project_equipments_type_grouping;
上面这种写法基本能满足我们的要求了,但本着闲着也是闲着的好学态度,又写了第三种解法:
select pid as id ,count(*) as totalcount from project_equipments_type_grouping group by pid;
上述解法仅供参考,上面的别名和分组方式只是为了方便本人结果展示。