mysql - 关于连接表的 SQL

Question

有 2 个表 - table1 和 table2 我想知道相应范围内的等级的计数

表格1

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║ 80  ║
║  2 ║ 75  ║
║  3 ║ 72  ║
║  4 ║ 85  ║
║  5 ║ 70  ║
║  6 ║ 80  ║
║  7 ║ 76  ║
╚════╩═════╝

表2

╔═══════╦══════════╗
║ grade ║ boundary ║
╠═══════╬══════════╣
║  A    ║ 85       ║
║  B    ║ 80       ║
║  C    ║ 75       ║
║  D    ║ 70       ║
║  E    ║ 65       ║
║  F    ║  0       ║
╚═══════╩══════════╝

我怎样才能得到这样的结果：

╔═══════╦════════╗
║ grade ║ total  ║
╠═══════╬════════╣
║  A    ║ 1      ║
║  B    ║ 2      ║
║  C    ║ 2      ║
║  D    ║ 2      ║
║  E    ║ 0      ║
║  F    ║ 0      ║
╚═══════╩════════╝

我以前用 PHP 来做，但我只想用 SQL 来做，我不熟悉复杂的 join 语句。

标签： mysqlsqljoingroup-by

Answer 1

select table2.grade, ifnull(total, 0) as total from table2 left join (
    select grade, count(*) as total from (
        select table2.grade from table1 join table2
        on table2.boundary = (select max(boundary) from table2 where boundary <= table1.val)
    ) sq
    group by grade
) sq on table2.grade = sq.grade
order by grade;

见 DB 小提琴