如何获取MySql中每列的前N个值 [英] How to get the top N values of each column in MySql

问题描述

我有一个包含 50 列和 1000 行的表格.我想输出每列的前 5 条记录.要获得 1 条记录，我会这样做:

I have a table with 50 columns and 1000s of rows. I want to output the top 5 records for each column. To get 1 record I do:

SELECT MAX(column1), MAX(column2), MAX(column3) FROM table

这会获得每一列的最高值，但我怎样才能获得第二个最大值"n 次?

This gets the top value for each column but how can I get the second "max" value n times?

推荐答案

这不是您想在单个查询中完成的事情.只需将其分解，每列一个查询.在正确的情况下(也就是使用正确的索引和正确的列类型)，MySQL 实际上可以通过短路来优化这些查询，这样它就不必扫描整个表，它只是提取前 5 个值并完成.

This is NOT something you want to do in a single query. Just break it out, one query for each column. Under the right circumstances (aka with the right indexes and right column types), MySQL can actually optimize these queries by short circuiting so that it never has to scan the whole table, it just pulls out the top 5 values and done.

SELECT column1 FROM table ORDER BY column1 DESC LIMIT 5
SELECT column2 FROM table ORDER BY column2 DESC LIMIT 5
etc

如果您尝试将它们全部合并为一个庞大的、笨拙的查询，您只能设法说服优化器放弃并重新扫描整个表 50 次，然后使用 50 个临时表，可能还有一些文件排序措施.因此，除非您的表中有大约 10 行(显然没有)，否则 50 个单独的查询总是会更快.

If you try to smoosh them all together into one giant, kludgy query you'll only manage to convince the optimizer to just give up and rescan the whole table 50 times and then use 50 temp tables and probably some file sorting for good measure. So unless your table has about 10 rows in it (which it obviously doesn't), 50 separate queries will always be faster.

这篇关于如何获取MySql中每列的前N个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！