在三个不同的列中按最大值排序 [英] Order by max value in three different columns

问题描述

我什至不确定是否可以执行此操作，但是我想基于三列之一的最大值对查询进行排序.

表结构示例: guid，column1，column2，column3

第1-3列具有数值，我想基于1、2或3的最大值对select语句进行排序.

例如:

record column1  column2  column3    
---------------------------------
1      5        0        2
2      2        0        6
3      0        1        2

将对记录2、1、3进行排序，因为6是三个记录中三个字段的最大值，记录1是第二个，记录3是第三个.

这有意义吗?

谢谢.

解决方案

在选择查询中可以做到 (可能使用case when之类的方法，尽管我不确定是否允许在order by子句本身中，YMMV取决于DBMS)，但是如果希望数据库随着表的增大而很好地扩展，则使用逐行计算并不是一个好主意(不具备单腿猪的性能)在赛马中"，正如我们的一名DBA雄辩地说的那样.

在这种情况下，我设置了一个额外的(索引)列以保留最大值，并通过使用插入/更新触发器将新列强制为其他三个列的最大值来确保数据完整性得到保持. /p>

由于大多数数据库表的读取次数远多于写入的次数，因此分摊了所有读取次数的计算成本.仅当更新数据并且查询变得令人眼花fast乱时才承担费用，因为您要对单个索引列进行排序:

select f1, f2, f3 from t order by fmax desc;

I'm not even sure it's possible to do this but I want to order a query based on the maximum value of one of three columns.

Example table structure: guid, column1, column2, column3

Columns 1-3 have numerical values and I want to order the select statement based on the maximum value of 1, 2 OR 3.

For example:

record column1  column2  column3    
---------------------------------
1      5        0        2
2      2        0        6
3      0        1        2

Would be ordered record 2, 1, 3 because 6 is the maximum value of the three fields across the three records, record 1 is the second and record 3 is the third.

Does this make any sense?

Thanks.

解决方案

It may be possible to do in a select query (possibly using something like case when though I'm not sure that's allowed in the order by clause itself, YMMV depending on the DBMS) but it's rarely a good idea to use per-row calculations if you want your database to scale well as tables get bigger ("not have the performance of a one-legged pig in a horse race", as one of our DBAs eloquently puts it).

In situations like this, I set up an additional (indexed) column to hold the maximum and ensure that the data integrity is maintained by using an insert/update trigger to force that new column to the maximum of the other three.

Because most database tables are read far more often than written, this amortises the cost of the calculation across all the reads. The cost is borne only when the data is updated and the queries become blindingly fast since you're ordering on a single, indexed, column:

select f1, f2, f3 from t order by fmax desc;

这篇关于在三个不同的列中按最大值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！