PHP/MySQL：筛选符合条件的Item并统计去重总数的最优方法

表结构与数据 #

+-------------+--------------+-----------+------------+----------------+--------------------+
| Chapter(INT)| Unit (INT)   | Item (INT)| WordNo(INT)| Word (VCHAR)   |PartofSpeech(VCHAR) |
+-------------+--------------+-----------+------------+----------------+--------------------+
| 4           | 1            | 1         | 1          | He             | pronoun            |
| 4           | 1            | 1         | 2          | sells          | verb               |
| 4           | 1            | 1         | 3          | Apples         | noun               |
| 4           | 1            | 2         | 1          | Those          | pronoun            |
| 4           | 1            | 2         | 2          | apples         | noun               |
| 4           | 1            | 2         | 3          | are            | verb               |
| 4           | 1            | 2         | 4          | red            | adjective          |
| 4           | 1            | 3         | 1          | Green          | adjective          |
| 4           | 1            | 3         | 2          | grapes         | noun               |
| 4           | 1            | 3         | 3          | are            | verb               |
| 4           | 1            | 3         | 4          | delicious      | adjective          |
| 4           | 1            | 4         | 1          | These          | pronoun            |
| 4           | 1            | 4         | 2          | apples         | noun               |
| 4           | 1            | 4         | 3          | are            | verb               |
| 4           | 1            | 4         | 4          | rotten         | adjective          |
+------------------------------------------------------------------------------+

需求说明 #

• 筛选**同时包含Word='apples'和PartofSpeech='adjective'**的唯一Item分组
• 统计这类Item的总数（预期结果：2）
• 返回对应Item的完整句子（如Item2的"Those apples are red."、Item4的"These apples are rotten."）

用户尝试的错误查询 #

SELECT SUM(itemnum) AS totalitems
FROM (SELECT COUNT(DISTINCT Item) AS itemnum 
FROM EnglishLevel1
WHERE Word = 'apples'
AND PartofSpeech = 'adjective'
GROUP BY Chapter, Unit, Item
) t

解决方案 #

核心问题分析 #

原查询用AND同时匹配Word='apples'和PartofSpeech='adjective'，但同一行记录不可能同时满足这两个条件（apples的词性是noun），因此无法返回有效数据。正确思路是对Item分组后，判断该分组内是否同时存在符合两个条件的记录。

最优查询方案（一步获取总数与句子） #

SELECT 
    COUNT(DISTINCT el1.Item) AS totalitems,
    GROUP_CONCAT(
        CONCAT(
            (SELECT GROUP_CONCAT(Word ORDER BY WordNo SEPARATOR ' ') 
             FROM EnglishLevel1 el2 
             WHERE el2.Item = el1.Item),
            '.'
        ) SEPARATOR '\n'
    ) AS sentences
FROM EnglishLevel1 el1
WHERE el1.Item IN (
    -- 子查询筛选同时包含apples和adjective的Item
    SELECT Item
    FROM EnglishLevel1
    GROUP BY Chapter, Unit, Item
    HAVING SUM(CASE WHEN Word = 'apples' THEN 1 ELSE 0 END) > 0
       AND SUM(CASE WHEN PartofSpeech = 'adjective' THEN 1 ELSE 0 END) > 0
);

分步解释 #

1. 子查询筛选目标Item：通过GROUP BY Chapter, Unit, Item分组，用HAVING子句统计每个分组中Word='apples'和PartofSpeech='adjective'的记录数，只要两者都大于0，说明该Item符合要求。
2. 拼接完整句子：对每个符合条件的Item，嵌套子查询用GROUP_CONCAT按WordNo顺序拼接单词，再添加句点形成完整句子。
3. 统计总数：用COUNT(DISTINCT Item)直接统计符合条件的Item数量。

可选：分开获取总数与句子 #

如果需要分别输出总数和句子，可以拆分两个查询：

• 统计总数：

SELECT COUNT(DISTINCT Item) AS totalitems
FROM EnglishLevel1
GROUP BY Chapter, Unit, Item
HAVING SUM(CASE WHEN Word = 'apples' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN PartofSpeech = 'adjective' THEN 1 ELSE 0 END) > 0;

• 获取对应句子：

SELECT 
    Item,
    CONCAT(GROUP_CONCAT(Word ORDER BY WordNo SEPARATOR ' '), '.') AS sentence
FROM EnglishLevel1
WHERE Item IN (
    SELECT Item
    FROM EnglishLevel1
    GROUP BY Chapter, Unit, Item
    HAVING SUM(CASE WHEN Word = 'apples' THEN 1 ELSE 0 END) > 0
       AND SUM(CASE WHEN PartofSpeech = 'adjective' THEN 1 ELSE 0 END) > 0
)
GROUP BY Item;

内容的提问来源于stack exchange，提问作者Niwa