MySQL: Writing a complex query

问题:

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

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

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

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) You will get a list of all articles which contain this particular word from table "Words"

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

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?

回答1:

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;

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

回答2:

Answer for 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.

SELECT ar.content 
FROM article ar INNER JOIN words ws ON ar.idArticle = ws.idArticle 
  INNER JOIN words_learned wl ON wl.idwords = ws.idwords AND wl.userid = @givenID
ORDER BY ar.content DESC

Here @givenID is provided user ID and to my understanding content will hold value of learnt words

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

SELECT 
    * 
FROM 
    article ar 
INNER JOIN 
    words ws 
ON 
    ar.idArticle = ws.idArticle 
AND 
    ws.idwords is like '%pe%'

this will select all the words having substring pe like sniper etc . Also you can select only article content by replacing * with ar.content