mysql - 选择过滤条件及其计数

Question

我有两个表:

表格过滤器

id   INT(11) PRIMARY KEY
text VARCHAR(50)

表格项目

id    INT(11) PRIMARY KEY
title VARCHAR(255)

我想显示所有过滤条件并计算它们在标题中的出现次数

我目前正在使用此语句，但我得到的计数为零

SELECT  filters.text, 
        (SELECT COUNT(items.id) 
         FROM items 
         WHERE (items.title LIKE 'filters.text' OR 
                items.title LIKE '%filters.text' OR 
                items.title LIKE '%filters.text%' OR 
                items.title LIKE 'filters.text%')
        ) AS count
        FROM filters, items
GROUP BY filters.ID
ORDER BY filters.ID DESC

Answer 1

试一试

SELECT  a.text, COUNT(b.id) totalCount
FROM    filters a
        LEFT JOIN items b
            ON b.title LIKE CONCAT('%',a.text,'%')
GROUP BY a.text
ORDER BY totalCount DESC

• > SQLFiddle Demo Link