数据库实战踩坑指南1：取前N项，需要WITH TIES

港控/mmm° 2023-10-03 10:26 40阅读 0赞

在sql查询中，取前N项几乎是最为入门的操作了，怎么这里也有坑吗？

当然有的哦！

一个最简单的场景，我们企图在一个学生成绩表中查询前三名。语句应该类似于：

// SQL server：
    Select top 3 * from 成绩表 order by 成绩 desc  
    
    // MySQL、PGSql：
    SELECT * FROM 成绩表  order by 成绩 desc LIMIT 3

上面的查询，将会得到3条记录（假设记录数大于3条）。

但是：有没有想到有这样一种场景：有5个人并列第三名了，语句的执行结果还符合预期吗？

这时，我们需要使用SQL:2008标准里引入的关键字“WITH TIES”了。它将会带来“并列第三名”的查询效果——当然，出于可以理解的原因，这个关键字必须和order by联合使用。同时，需要注意在不同的数据库上，对标准的实现是各不相同的。

如Sql server 的实现，看起来就比较简练，毕竟少了Fetch关键词嘛！

Select top 3 with ties * from 成绩表 order by 成绩 desc

而PgSQL （13及更高）看起来就比较冗长。留意limit关键字已经被直接替换了

SELECT * FROM 成绩表  order by 成绩 desc fetch first 3 row with ties

上述查询在有5个人并列第三名的时候（假设第一第二名均无并列），将会得到7行记录。

至于MySQL，嗯，那就是一个小小的挑战喽——它还不支持该功能，只能用子查询多查一次了。

SELECT * from 成绩表 where 分数 >=
    	(SELECT min(分数) FROM 成绩表  order by 成绩 desc LIMIT 3) 
    order by 成绩 desc

其子查询先得到第三名的分数，然后将大于该分数的数据查出来并排序。读着累，写着更累。

传送门：

[数据库实战踩坑指南1：取前N项，需要WITH TIES][1_N_WITH TIES]

[https://dev.mysql.com/doc/refman/8.0/en/select.html][https_dev.mysql.com_doc_refman_8.0_en_select.html]

[https://www.postgresql.org/docs/13/sql-select.html][https_www.postgresql.org_docs_13_sql-select.html]

[https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15][https_docs.microsoft.com_en-US_sql_t-sql_queries_select-transact-sql_view_sql-server-ver15]

[1_N_WITH TIES]: https://zhuanlan.zhihu.com/p/340156681?utm_source=ZHShareTargetIDMore&utm_medium=social&utm_oi=854602979624497152
[https_dev.mysql.com_doc_refman_8.0_en_select.html]: https://dev.mysql.com/doc/refman/8.0/en/select.html
[https_www.postgresql.org_docs_13_sql-select.html]: https://www.postgresql.org/docs/13/sql-select.html
[https_docs.microsoft.com_en-US_sql_t-sql_queries_select-transact-sql_view_sql-server-ver15]: https://docs.microsoft.com/en-US/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15