我很想拥有一个子查询，该查询将获取一个人 学习的所有单词并将其与自身相结合，并带有GROUP_CONCAT单词 和一个计数。所以给：
Octopus, NULL, 0
Dog, "Octopus", 1
Spoon, "Octopus,Dog", 2
因此，子查询将类似于：
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
giving
idwords excl_words older_words_cnt
1 NULL 0
2 1 1
3 1,2 2
然后将其结果与其他表结合起来，检查 主要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_words
INNER JOIN words words_inc
ON sub_words.idwords = words_inc.idwords
LEFT OUTER JOIN words words_exc
ON words_inc.idArticle = words_exc.idArticle
AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)
WHERE words_exc.idwords IS NULL
ORDER BY older_words_cnt
LIMIT 100
编辑-已更新，以排除 尚未学习的超过10个单词的文章。
SELECT sub_words.idwords, words_inc.idArticle,
sub2.idArticle, sub2.count, sub2.content
FROM
(
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_inc
ON sub_words.idwords = words_inc.idwords
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
) sub2
ON words_inc.idArticle = sub2.idArticle
LEFT OUTER JOIN words words_exc
ON words_inc.idArticle = words_exc.idArticle
AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)
WHERE words_exc.idwords IS NULL
ORDER BY older_words_cnt
LIMIT 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.content
FROM
此子查询获取每个已学习的单词，以及以逗号分隔 的具有较大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_inc
ON 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
) sub2
ON words_inc.idArticle = sub2.idArticle
该联接用于从第一个子查询中查找在逗号分隔列表中包含单词的文章（即，order_learned较大的单词）。这是作为LEFT OUTER JOIN完成的，因为我想排除找到的任何单词（这在 WHERE子句中通过检查NULL来完成）
LEFT OUTER JOIN words words_exc
ON words_inc.idArticle = words_exc.idArticle
AND FIND_IN_SET(words_exc.idwords, sub_words.excl_words)
WHERE words_exc.idwords IS NULL
ORDER BY older_words_cnt
LIMIT 100
602392714
清零编程群