如何用MySQL的FIND_IN_SET和LEFT JOIN统计分类下的广告数量？

Hey there, let's tackle your MySQL questions one by one, nice and clear!

First, a quick recap: FIND_IN_SET(search_value, comma_separated_string) returns the position of search_value in the comma-separated list (starts at 1), or 0 if it's not present.

Let's assume you have two tables:

• categories: Has category_id (unique ID for each category) and category_name
• ads: Has ad_id and category_ids (a comma-separated string of category IDs the ad belongs to, e.g., "1,3,5")

Count ads for a single specific category #

If you want to count how many ads belong to category ID 2, use this query:

SELECT COUNT(*) AS ad_count
FROM ads
WHERE FIND_IN_SET(2, category_ids) > 0;

The > 0 checks that the category exists in the ad's category_ids list, then COUNT(*) tallies all matching ads.

Count ads for all categories (grouped) #

To get a breakdown of ad counts per category (including categories with 0 ads), join the categories table with ads using FIND_IN_SET in the join condition:

SELECT 
  c.category_id,
  c.category_name,
  COUNT(a.ad_id) AS ad_count
FROM categories c
LEFT JOIN ads a ON FIND_IN_SET(c.category_id, a.category_ids) > 0
GROUP BY c.category_id, c.category_name;

Using COUNT(a.ad_id) instead of COUNT(*) ensures categories with no ads return 0 (since a.ad_id will be NULL for those, and COUNT(column) ignores NULL values).

From what you described, you're getting repeated inflated counts (like 2, 2, 2) when you only expect a valid count for one category. Let's break down the issue and fix it.

The problem #

When you use LEFT JOIN with FIND_IN_SET, if an ad belongs to multiple categories, the join will create a separate row for each category-ad match. Using COUNT(*) counts all these rows—including NULL rows for categories with no ads—leading to incorrect numbers.

For example: If one ad is linked to categories 1, 2, and 3, the join produces 3 rows (one for each category). COUNT(*) would count each row, making it look like each category has 1 ad, even if only that single ad exists.

The fix #

Use COUNT(DISTINCT a.ad_id) instead of COUNT(*) if you want to count unique ads per category (in case an ad is listed multiple times in the ads table). Or use COUNT(a.ad_id) if you just want to count valid ad matches (ignoring NULLs for categories with no ads).

Here's the corrected query:

SELECT 
  c.category_id,
  COUNT(DISTINCT a.ad_id) AS ad_count
FROM categories c
LEFT JOIN ads a ON FIND_IN_SET(c.category_id, a.category_ids) > 0
GROUP BY c.category_id;

This gives you accurate numbers: if one ad is in 3 categories, each category's count increments by 1 (correct if you want to count that ad towards every category it's part of). If you had duplicate ad entries, DISTINCT ensures they're only counted once.

内容的提问来源于stack exchange，提问作者Kenan Şimşek Birusk Kuresofa