编写复杂的MySQL查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了编写复杂的MySQL查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5629字，纯文字阅读大概需要9分钟。

内容图文

注意：您可以在这里找到我之前的问题及其答案-MySQL: Writing a complex query

我有3张桌子.

表Words_Learned包含用户已知的所有单词以及单词学习的顺序.它有3列：1)单词ID和2)用户ID,以及3)学习单词的顺序.

表文章包含文章.它具有3列：1)文章ID,2)唯一字数和3)文章内容.

表格词包含每篇文章中所有唯一词的列表.它有2列1)单词ID和2)文章ID

数据库图如下/

您可以从此处下载数据库代码：https://www.dropbox.com/s/3gr659y5mk05i5w/tests.sql?dl=0

现在,使用此数据库并使用“仅” mysql,我需要完成以下工作.

给定一个用户ID,它应该获得该用户已知的所有单词的列表,并按照从中获悉的可追溯顺序排序.换句话说,最新学习的单词将位于列表的顶部.

假设对用户ID的查询显示他们已经记住了以下3个单词,并且我们会跟踪他们学习单词的顺序.
章鱼-3
狗-2
汤匙-1

首先,我们获得所有包含“章鱼”一词的文章的列表,然后对这些文章使用表Words进行计算.计算意味着,如果该文章包含10个以上未出现在用户词汇表中的单词(从表格words_learned中拉出),则将其从列表中排除.

然后,我们对所有包含dog但不包含“章鱼”的记录进行查询

然后,我们对所有包含汤匙但不包含“章鱼”或“狗”的记录进行查询

您将继续执行此重复过程,直到找到100个符合此条件的记录.

为了实现这一过程,我做了以下工作

SELECT `words_learned`.`idwords`,
Words.`idArticle`
FROM words_learned
INNER JOIN Words ON Words.idWords = Words_Learned.`idwords`
WHERE words_learned.userId = 1
ORDER BY Words_Learned.`order` DESC

在我的查询中,我已经掩盖了获取文章的含义,这意味着到这里-首先,我们获得包含八达通一词的所有文章的列表,然后仅对这些文章使用表Words进行计算..但是我需要什么为了掩盖剩下的事？

更新

这是用于更好理解的伪代码.

Do while articles found < 100
{
 for each ($X as known words, in order that those words were learned)
 {
  Select all articles that contain the word $X, where the 1) article has not been included in any previous loops, and 2)where the count of "unknown" words is less than 10. 

  Keep these articles in order. 
 }
}

解决方法:

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

内容总结

以上是互联网集市为您收集整理的编写复杂的MySQL查询全部内容，希望文章能够帮你解决编写复杂的MySQL查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端