一条sql的部分结果集如何按照另外一张表排序

表结构说明：

1. word 表：记录单词学习次数

• word_id：单词ID
• times: 学习次数

2. word_book 表：记录单词在单词书中顺序

需求：

查询学习次数超过3次的单词，并按学习次数递增排序，如果单词是在book_id=1的书中，需要按sort排序

word 表测试数据：

word_book 表测试数据：

解决方案：

使用如下sql:

select * from word LEFT JOIN word_book 
on word.word_id=word_book.word_id and book_id=1 
where times>=3 ORDER BY times ASC,sort ASC;

查询结果如下，可以看到book_id=1 的单词是按 sort 再次排序的

但如果我们使用如下sql：

select * from word LEFT JOIN word_book 
on word.word_id=word_book.word_id 
where book_id=1 and times>=3 ORDER BY times ASC,sort ASC;

则只会查询到book_id=1 的结果：

可以看到where 和 on 的是有使用区别的

left join 中 where 和 on 的区别

引用这篇文章：mysql left join中where和on条件的区别

1. left join 会生成一张临时表，并返回给用户

2. where 是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。

3. on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL

4. on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行，但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。

5. 结论：

   • where条件中对左表限制，不能放到on后面
   • where条件中对右表限制，放到on后面，会有数据行数差异，比原来行数要多

参考：

• mysql left join中where和on条件的区别
• MySQL LEFT JOIN 默认值，数据过滤，排序的处理