代码之家 › 专栏 › 技术社区 › Nick M

嵌套类别表上计数的自联接

self-join mariadb sql mysql

Nick M · 技术社区 · 7 年前

我有一个listing_categories表,其中有许多listing_计划和listings,以及许多子类别。我正试图编写一个视图,用列表计划计数、列表计数和子类别计数来显示列表类别。

这是我的物品分类表:

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 |
+----+-------------------+-------------------+---------------+-------------------+------------+

在上面的例子中,没有一个数字是正确的,例如,每个列表类别只有两个计划。

我做错什么了?

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

使用 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() 只计算非- NULL 价值观。您是沿着一个层次连接的,因此当您下降层次时,行会成倍增加。