MySQL:编写复杂的查询 [英] MySQL: Writing a complex query

问题描述

我有3张桌子.在这里，我要发布它的数据库图.

I have 3 tables. Here I am posting it's database diagram.

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

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

现在，使用此数据库并使用仅" mysql，我需要执行以下工作.

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

1)给定一个用户ID，它应该获得该用户已知的所有单词的列表，并按从中得知的顺序重新排列.换句话说，最近学到的单词将位于列表的顶部.

1) 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.

2)您将从单词"表中获得包含此特定单词的所有文章的列表.

2) You will get a list of all articles which contain this particular word from table "Words"

3)您将扫描此列表并返回表"Article"中的所有记录，其中最多包含10个未知"字.换句话说，如果该文章包含10个以上未出现在用户词汇表中的词(从表"Words_Learned"中拉出)，则该词将从列表中排除.

3) You will scan this list and return all records from table "Article" which contain a maximum of 10 "unknown" words. In other words, 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.

4)然后，您从步骤1移至列表中的下一条记录.您重复相同的过程，只是跳过了从步骤3返回或在步骤3过滤过程中排除的任何文章.

4) Then, you move on to the next record in the list from step step 1. You repeat the same process, except you skip any articles that were returned from step 3 or excluded as part of the filtering process in step 3.

为实现此过程，我做了以下

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

在我的查询中，我涵盖了第1点和第2点.但是，为了涵盖第3点和第4点，我需要做什么?

In my query, I have covered point 1 and 2. But what should I need to do in order to cover point 3 and 4?

推荐答案

换句话说:显示所有不存在难以阅读文章的学习单词.我没有按照建议一步一步地做.这是我的查询:

In other words: Show all learnt words for which no hard-to-read articles exist. I don't do it quite step by step as suggested. Here is my query:

select *
from words_learned
where userid = 1
and not exists
(
  -- word being used in at least one article with too many unknown words
  select *
  from words
  where words.idwords = words_learned.idwords
  and words.idarticle in
  (
    -- articles with more then 10 unknown words
    select w.idarticle
    from words w
    left join words_learned l on l.idwords = w.idwords and l.userid = 1
    group by w.idarticle
    having count(*) - count(l.idwords) > 10
  )
)
order by `order` desc;

这是一个SQL提琴: http://sqlfiddle.com/#!2/6de6a/4 .

Here is an SQL fiddle: http://sqlfiddle.com/#!2/6de6a/4.

这篇关于MySQL:编写复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！