mysql复杂查询和更新_编写复杂的MySQL查询

我很想拥有一个子查询,该查询可以获取一个人学习过的所有单词,并将其与自身结合在一起,并带有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

给

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

编辑-尝试评论以上查询：-

这只是选择列

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

这仅仅是为了获取文章中使用的单词(即从上述子查询中获悉的单词)

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