mysql - 如何获取父子关系的子数

Question

使用此查询将得到如下图链接所示的结果

select c.property_title,b.property_title as building,d.property_title as floor,e.house_name as house,
    f.room_no as room,g.bed_no as bed
    from g_property_group c
    left join g_property_group b on b.parent_id = c.id and b.is_deleted = '0'
    left join g_property_group d on d.parent_id = b.id and d.is_deleted = '0'
    left join g_house e on e.property_group_id = d.id and e.is_deleted = '0'
    left join g_room f on f.house_id = e.id and f.is_deleted = '0'
    left join g_bed g on  g.room_id = f.id and g.is_deleted = '0'
    where c.id = 'a976df373f75d3f8cc49938ae9fead8e4fc8ad19'
    and c.is_deleted = '0'

我已经编写了获取父子计数的查询。将得到建筑物计数为 7 层计数为 7 房屋计数为 5 房间计数为 4，而不是建筑物计数应为 2，楼层 = 2，房屋 = 4，房间 = 5，床 1。

select c.property_title,count(b.property_title)as building,count(d.property_title)as floor,count(e.house_name)as house,
count(f.room_no)as room,count(g.bed_no) as bed
from g_property_group c
left join g_property_group b on b.parent_id = c.id and b.is_deleted = '0'
left join g_property_group d on d.parent_id = b.id and d.is_deleted = '0'
left join g_house e on e.property_group_id = d.id and e.is_deleted = '0'
left join g_room f on f.house_id = e.id and f.is_deleted = '0'
left join g_bed g on g.house_id = g.id and g.room_id = f.id and g.is_deleted = '0'
where c.id = 'a976df373f75d3f8cc49938ae9fead8e4fc8ad19'
and c.is_deleted = '0'

提前谢谢

Answer 1

试试这个:

SQL 服务器

select count([building]) over (order by building) as [bulding count],
       count([floor]) over (order by [floor],[building]) as [floor count],
       count([house]) over (order by building,[floor],[house]) as [house count],
       count([room]) over (order by building,[floor],[house],[room]) as [room count],
       count([bed]) over (order by building,[floor],[house],[room],[bed]) as [room count]