我有一个listing_categories表，有许多listing_plans和列表，以及许多子类别。我正在尝试编写一个视图来显示具有listing_plan计数，列表计数和子类别计数的列表类别。
这是我的listing_categories表：
MariaDB [railsapp_development]> describe listing_categories;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| website_id | int(11) | YES | MUL | NULL | |
| listing_category_id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| slug | varchar(255) | YES | | NULL | |
| description | text | YES | | NULL | |
| category_card | text | YES | | NULL | |
| listing_card | text | YES | | NULL | |
| layout | varchar(255) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
| created_at | datetime | NO | | NULL | |
| updated_at | datetime | NO | | NULL | |
| keywords | varchar(255) | YES | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
我想写的观点：
create or replace view `listing_category_details` AS
select
cats1.*,
count(l.id) as listing_count,
count(cats2.id) as subcategory_count,
count(lp.id) as plan_count
from
listing_categories as cats1
left join
listings as l on cats1.id = l.listing_category_id
left join
listing_plans as lp on cats1.id = lp.listing_category_id
inner join
listing_categories as cats2 on cats1.listing_category_id = cats2.id
group by
cats1.id
order by
cats1.name asc;
输出不正确，没有显示正确的计数，如下所示：
MariaDB [railsapp_development]> select id, slug, name, listing_count, subcategory_count, plan_count from listing_category_details limit 10;
+----+-------------------+-------------------+---------------+-------------------+------------+
| id | slug | name | listing_count | subcategory_count | plan_count |
+----+-------------------+-------------------+---------------+-------------------+------------+
| 17 | ares | Ares | 22 | 22 | 22 |
| 30 | automotive | Automotive | 16 | 16 | 16 |
| 19 | crist-osinski-inc | Crist-Osinski Inc | 12 | 12 | 12 |
| 29 | esl-cologne | ESL Cologne | 20 | 20 | 20 |
| 18 | executive-office | Executive Office | 22 | 22 | 22 |
| 27 | gfinity-london | GFinity London | 24 | 24 | 24 |
| 25 | hephaestus | Hephaestus | 28 | 28 | 28 |
| 24 | iem-championship | IEM Championship | 14 | 14 | 14 |
| 26 | league-all-stars | League All Stars | 30 | 30 | 30 |
| 21 | machinery | Machinery | 14 | 14 | 14 |
+----+-------------------+-------------------+---------------+-------------------+------------+
在上面的例子中，没有一个数字是正确的，例如每个listing_category恰好有2个计划。
我究竟做错了什么？
分析解答
使用COUNT(DISTINCT)：
select cats1.*,
count(distinct l.id) as listing_count,
count(distinct cats2.id) as subcategory_count,
count(distinct lp.id) as plan_count
from listing_categories cats1 left join
listings l
on cats1.id = l.listing_category_id left join
listing_plans lp
on cats1.id = lp.listing_category_id inner join
listing_categories cats2
on cats1.listing_category_id = cats2.id
group by cats1.id
order by cats1.name asc;
COUNT()只计算non-NULL值的数量。您正在按层次结构加入，因此当您下降层次结构时，行会相乘。