我很想拥有一个子查询，该查询将获取一个人
学习的所有单词并将其与自身相结合，并带有GROUP_CONCAT单词
和一个计数。所以给：
Octopus, NULL, 0Dog, "Octopus", 1Spoon, "Octopus,Dog", 2
因此，子查询将类似于：
SELECt sub0.idwords, GROUP_CONCAt(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cntFROM words_learned sub0LEFT OUTER JOIN words_learned sub1ON sub0.userId = sub1.userIdAND sub0.order_learned < sub1.order_learnedWHERe sub0.userId = 1GROUP BY sub0.idwords
giving
idwords excl_words older_words_cnt1 NULL 02 1 13 1,22
然后将其结果与其他表结合起来，检查
主要idword匹配但没有其他匹配的文章。
像这样的东西（尽管没有作为测试数据进行测试）：
SELECt sub_words.idwords, words_inc.idArticle( SELECT sub0.idwords, SUBSTRING_INDEX(GROUP_CONCAt(sub1.idwords), ',', 10) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERe sub0.userId = 1 GROUP BY sub0.idwords) sub_wordsINNER JOIN words words_incON sub_words.idwords = words_inc.idwordsLEFT OUTER JOIN words words_excON words_inc.idArticle = words_exc.idArticleAND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)WHERe words_exc.idwords IS NULLORDER BY older_words_cntLIMIT 100
编辑-已更新，以排除
尚未学习的超过10个单词的文章。
SELECt sub_words.idwords, words_inc.idArticle,sub2.idArticle, sub2.count, sub2.contentFROM( SELECt sub0.idwords, GROUP_CONCAt(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERe sub0.userId = 1 GROUP BY sub0.idwords) sub_words INNER JOIN words words_incON sub_words.idwords = words_inc.idwordsINNER JOIN( SELECt a.idArticle, a.count, a.content, SUM(IF(c.idwords_learned IS NULL, 1, 0)) AS unlearned_words_count FROM Article a INNER JOIN words b ON a.idArticle = b.idArticle LEFT OUTER JOIN words_learned c ON b.idwords = c.idwords AND c.userId = 1 GROUP BY a.idArticle, a.count, a.content HAVINg unlearned_words_count < 10) sub2ON words_inc.idArticle = sub2.idArticleLEFT OUTER JOIN words words_excON words_inc.idArticle = words_exc.idArticleAND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)WHERe words_exc.idwords IS NULLORDER BY older_words_cntLIMIT 100
EDIT - attempt at commenting the above query:-
This just selects the columns
SELECT sub_words.idwords, words_inc.idArticle,sub2.idArticle, sub2.count, sub2.contentFROM
此子查询获取每个已学习的单词，以及以逗号分隔
的具有较大order_learned的单词列表。这是针对特定的用户
ID
( SELECt sub0.idwords, GROUP_CONCAt(sub1.idwords) AS excl_words, COUNT(sub1.idwords) AS older_words_cnt FROM words_learned sub0 LEFT OUTER JOIN words_learned sub1 ON sub0.userId = sub1.userId AND sub0.order_learned < sub1.order_learned WHERe sub0.userId = 1 GROUP BY sub0.idwords) sub_words
This is just to get the articles the words (ie, the words learned from the
above sub query) are used in
INNER JOIN words words_incON sub_words.idwords = words_inc.idwords
此子查询获取的文章中少于10个单词的文章
尚未被特定用户学习。
INNER JOIN( SELECt a.idArticle, a.count, a.content, SUM(IF(c.idwords_learned IS NULL, 1, 0)) AS unlearned_words_count FROM Article a INNER JOIN words b ON a.idArticle = b.idArticle LEFT OUTER JOIN words_learned c ON b.idwords = c.idwords AND c.userId = 1 GROUP BY a.idArticle, a.count, a.content HAVINg unlearned_words_count < 10) sub2ON words_inc.idArticle = sub2.idArticle
该联接用于从第一个子查询中查找在逗号分隔列表中包含单词的文章（即，order_learned较大的单词）。这是作为LEFT OUTER JOIN完成的，因为我想排除找到的任何单词（这在
WHERe子句中通过检查NULL来完成）
LEFT OUTER JOIN words words_excON words_inc.idArticle = words_exc.idArticleAND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)WHERe words_exc.idwords IS NULLORDER BY older_words_cntLIMIT 100
