计算外键的数量 [英] count number of foreign keys

问题描述

我有三张桌子.

i have three tables.

1.fi_category

+----+-----------------+-----------------+
| id | name            | slug            |
+----+-----------------+-----------------+

2.fi_subcategory

+----+-----------------+-----------------+-------------+
| id | name            | slug            | category_id |
+----+-----------------+-----------------+-------------+

3.fi_business_subcategory

+----+-------------+----------------+
| id | business_id | subcategory_id |
+----+-------------+----------------+

我基本上想做的是，

what i am basically trying to do is,

1. 获取所有类别
2. 获取属于类别的所有子类别.
3. 计算特定子类别存在的业务数量.

这就是我尝试做的.

SELECT 
    f.id,
    f.name,
    f.slug,
    f2.id,
    f2.name,
    f2.slug,
    COUNT(f3.business_id) as count
FROM
    fi_category f
LEFT JOIN 
    fi_subcategory f2 ON f.id = f2.category_id
LEFT JOIN 
    fi_business_subcategory f3 ON f2.id = f3.subcategory_id

但是上面的查询只获取了 1 条记录.我如何获取我想要的东西?

however the above query fetches only 1 record. how do i fetch what i want?

推荐答案

我会添加一个 GROUP BY 子句:

I would add a GROUP BY clause:

SELECT 
    f.id,
    f.name,
    f.slug,
    f2.id,
    f2.name,
    f2.slug,
    COUNT(f3.business_id) as count
FROM fi_category f
LEFT JOIN fi_subcategory f2 
    ON f.id = f2.category_id
LEFT JOIN fi_business_subcategory f3 
    ON f2.id = f3.subcategory_id
GROUP BY f.id,
    f.name,
    f.slug,
    f2.id,
    f2.name,
    f2.slug

或者在子查询中获取您的 count():

Or get your count() in a sub-query:

SELECT f.id,
    f.name,
    f.slug,
    f2.id,
    f2.name,
    f2.slug,
    f3.cnt
FROM fi_category f
LEFT JOIN fi_subcategory f2 
    ON f.id = f2.category_id
LEFT JOIN
(
    select count(business_id) cnt, subcategory_id 
    from fi_business_subcategory
    group by subcategory_id
) f3 
    ON f2.id = f3.subcategory_id

这篇关于计算外键的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！