选择查询并根据条件进行计数

问题描述：

我想选择所有类别，子类别并计算属于该子类别的业务数量。这是我正在使用的SQl查询。

I want to select all categories, subcategories and count the number of business that belongs to subcategory. this is the SQl query i am using.

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    COUNT(bsc.id) AS business_count
FROM 
    fi_category c
LEFT JOIN 
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) 
LEFT JOIN 
    fi_business_subcategory bsc ON sc.id = bsc.subcategory_id AND (bsc.deleted_at IS NULL) 
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

但是我想做的更多，business_count应该根据它们所属的城市进行过滤，即最终我要选择所有类别，子类别，但business_count应该具有类似 WHERE city.id = 1 的子句，为此，我想我必须使用count作为子查询，但我无法弄清楚

however there is more i want to do, business_count should be filtered according to the city they belong i.e in the end i want to select all category, subcategory but business_count should have a clause like WHERE city.id = 1, for this i guess i have to use count as subquery which i am not been able to figure out.

以下是从 fi_business_subcategory 到 fi_city 。

1） fi_business_subcategory

1) fi_business_subcategory

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

2） fi_business

2) fi_business

+----+---------+-----------+
| id | name    | suburb_id |
+----+---------+-----------+

3） fi_suburb

3) fi_suburb

+-----+--------+---------+
| id  | name   | city_id |
+-----+--------+---------+

4） fi_city

4) fi_city

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

我尝试过像这样，但这似乎不起作用

i tried something like this, but this doesn't seem to work

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    bsc.business_count
FROM 
    fi_category c
LEFT JOIN 
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL) 
LEFT JOIN (
    SELECT 
        COUNT(business_id) t1.business_count, t1.subcategory_id 
    FROM
        fi_business_subcategory t1
    LEFT JOIN
        fi_business t2 ON t2.id = t1.business_id
    LEFT JOIN
        fi_suburb t3 ON t3.id = t2.suburb_id
    LEFT JOIN
        fi_city t4 ON t4.id = t3.city_id
    WHERE
        t4.id = 1
    GROUP BY
        t1.subcategory_id
) bsc ON sc.id = bsc.subcategory_id AND (bsc.deleted_at IS NULL)
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

我应该如何建立查询以实现我想要的？

how should i build up the query to achieve what i want?

答

我认为没有理由必须使用子查询。我相信您可以将 fi_business 和 fi_business_subcategory 合并到一个带括号的表格因子中。

I see no reason why you should have to use a subquery. I believe that you can simply combine fi_business and fi_business_subcategory to a single parenthesized table factor.

SELECT
    c.id, 
    c.name,
    c.slug,
    sc.id,
    sc.name,
    sc.slug,
    COUNT(bsc.id) AS business_count
FROM
    fi_category c
LEFT JOIN
    fi_subcategory sc ON c.id = sc.category_id AND (sc.deleted_at IS NULL)
LEFT JOIN (
        fi_business b
    INNER JOIN
        fi_business_subcategory bsc ON b.id = bsc.business_id AND (bsc.deleted_at IS NULL)
    INNER JOIN
        fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1
    ) ON sc.id = bsc.subcategory_id
WHERE 
    (c.deleted_at IS NULL) 
GROUP BY 
    c.id, sc.id

我已经选中了，对于这是有效的SQL您的表格结构。我想即使您的小提琴尚不包含任何数据，也很有可能会达到预期的结果。有关可以在何处使用的详细信息，请参见有关JOIN语法的手册。

I've checked that this is valid SQL for your table structure. I guess chances are good that it will yield the desired result, even though your fiddle doesn't contain any data yet. See the manual on JOIN syntax for details on where you can use parentheses in a join.

您可能还会问自己，是否真的需要将所有联接保留为联接。使用内部联接写东西会容易得多。

You might also ask yourself if you really need all the joins to be left joins. Writing things using inner joins would be much easier.

由于联接是从左到右执行的，因此您可以先执行内部联接，然后依次执行 right 加入。这样就避免了括号：

As joins are executed left to right, you might do the inner joins first, followed by a sequence of right joins. This avoids the parentheses:

SELECT
    c.id cat_id,
    c.name cat_name,
    c.slug cat_slug,
    sc.id sub_id,
    sc.name sub_name,
    sc.slug sub_slug,
    COUNT(bsc.id) AS business_count
FROM
    fi_business b
INNER JOIN
    fi_business_subcategory bsc ON b.id = bsc.business_id
    AND (b.deleted_at IS NULL) AND (bsc.deleted_at IS NULL)
INNER JOIN
    fi_suburb su ON su.id = b.suburb_id AND su.city_id = 1
RIGHT JOIN
    fi_subcategory sc ON sc.id = bsc.subcategory_id
RIGHT JOIN
    fi_category c ON c.id = sc.category_id AND (sc.deleted_at IS NULL)
WHERE
    (c.deleted_at IS NULL)
GROUP BY
    c.id, sc.id

选择查询并根据条件进行计数

相关推荐