编写复杂的MySQL查询 [英] Writing a Complex MySQL Query

问题描述

这不是重复的，您可以在这里找到上一个问题及其答案 -

现在，使用这个数据库并使用只有mysql，我需要做以下工作。 p>

给定一个用户ID，它应该获得该用户已知的所有单词的列表，按他们被学习的revese顺序排序。换句话说，最近学习的单词将在列表的顶部。

我们假设对用户ID的查询显示他们记住了以下3个单词，我们跟踪他们学习单词的顺序。
章鱼 - 3
狗 - 2
Spoon - 1

首先，我们得到包含Octopus然后只对那些文章使用表 Words 进行计算。计算意味着如果该文章包含超过10个不出现在用户词汇表中的单词（从表 words_learned 中提取），那么它将从列表中排除。

然后，我们对包含dog但不包含章鱼的所有记录执行查询

然后，我们对包含spoon的所有记录执行查询，但不包含单词Octopus或Dog

并且继续执行此重复过程，直到找到100符合此条件的记录。

为了实现这个过程，我做了下面的

  words_learned`.`idwords`，
 Words.`ArtArt` 
 FROM words_learned 
 INNER JOIN Words On Words.idWords = Words_Learned.`idwords` 
 WHERE words_learned.userId = 1 
 ORDER BY Words_Learned.`order` DESC 
  

在我的查询中，我覆盖了文章，这意味着这里 - 首先，我们得到所有包含单词Octopus的文章的列表，然后使用表单上的文字计算。。但是，我需要做什么来覆盖其余的？请帮忙。
。

更新

这里是一个phudocode 。

  100 
 {
每个（$ X作为已知的单词，以便那些单词被学习）
 {
选择所有包含单词$ X的文章，其中1）文章没有包括在任何以前的循环中，和2）其中未知单词的计数小于10. 
 
保持这些文章顺序。 
} 
} 
  

解决方案

I将试图获得一个子查询，获取一个人已经学习的所有单词，并加入它本身，与单词GROUP_CONCAT一起计数。所以给予： -

 章鱼，NULL，0 
狗，章鱼，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 old_words_cnt 
 1 NULL 0 
 2 1 1 
 3 1， 2 2 
  

然后将此结果与其他表结合，检查主要idwords匹配的文章

这样的东西（虽然没有被测试为没有测试数据）： -

  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 
  

EDIT - 已更新以排除包含超过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 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 - 尝试评论上述查询： -

这只是选择列

  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 
  

  INNER JOIN 
（
 SELECT a.idArticle，a.count，a.content，SUM（IF（c.idwords_learned IS NULL，1,0））AS unlearned_words_count 
 FROM 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.id文章，a.count，a.content 
 HAVING unlearned_words_count< 10 
）sub2 
 ON words_inc.idArticle = sub2.idArticle 
  

是从第一个子查询（即具有较大order_learned的单词）中查找在逗号分隔列表中具有单词的文章。这是做为一个LEFT OUTER JOIN，因为我想排除找到的任何单词（这是通过检查NULL在WHERE子句中完成）

  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 old_words_cnt 
 LIMIT 100 
  

This is not a duplicate, you can find the previous question and its answer here - MySQL: Writing a complex query

I have 3 tables.

Table Words_Learned contains all the words known by a user, and the order in which the words were learned. It has 3 columns 1) word ID and 2)user id and 3) order in which the word was learned.

Table Article contains the articles. It has 3 columns 1) article ID, 2) unique word count and 3) article contents.

Table Words contains a list of all unique words contained in each article. It has 2 columns 1) word ID and 2) article ID

The database diagram is as below/

You can download the DB code from here: https://www.dropbox.com/s/3gr659y5mk05i5w/tests.sql?dl=0

Now, using this database and using "only" mysql, I need to do the below work.

Given a user ID, it should get a list of all words known by this user, sorted in the revese order from which they were learned. In other words, the most recently learned words will be at the top of the list.

Let’s say that a query on a user ID shows that they’ve memorized the following 3 words, and we track the order in which they’ve learned the words. Octopus - 3 Dog - 2 Spoon - 1

First we get a list of all articles containing the word Octopus, and then do the calculation using table Words on just those articles. Calculation means if that article contains more than 10 words that do not appear in the user’s vocabulary list (pulled from table words_learned), then it is excluded from the listing.

Then, we do a query for all records that contain dog, but DO NOT contain "octopus"

Then, we do a query for all records that contain spoon, but DO NOT contain the words Octopus or Dog

And you keep doing this repetitive process until we’ve found 100 records that meet this criteria.

To achieve this process, I did the below

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

In my query, I have covered up to getting the articles, which means to here - First we get a list of all articles containing the word Octopus, and then do the calculation using table Words on just those articles.. But what should I need to do in order to cover the rest? Please help. .

Update

Here is a phudocode for better understanding.

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. 
 }
}

解决方案

I would be tempted to have a sub query that gets all the words a person has learned and join that against itself, with the words GROUP_CONCAT together along with a count. So giving:-

Octopus, NULL, 0
Dog, "Octopus", 1
Spoon, "Octopus,Dog", 2

So the sub query would be something like:-

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

Then join the results of this against the other tables, checking for articles where the main idwords matches but none of the others are found.

Something like this (although not tested as no test data):-

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 

EDIT - updated to exclude articles with more than 10 words that are not already learned.

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

This sub query gets each of the words learnt, along with a comma separated list of the words with a larger order_learned. This is for a particular user 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

This sub query gets the articles which have a less than 10 words in them that are not yet learnt by the particular user.

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

This join is to find articles that have words in the comma separted list from the 1st sub query (ie words with a larger order_learned). This is done as a LEFT OUTER JOIN as I want to exclude any words that are found (this is done in the WHERE clause by checking for 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查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！