如何在MySQL中最多可以为null的两列排序? [英] How to order by maximum of two column which can be null in MySQL?

问题描述

create table jobs(
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        .....
        salaryminus INTEGER UNSIGNED DEFAULT NULL,
        salaryplus INTEGER UNSIGNED DEFAULT NULL,
        .....
);

我想做类似的事情:

Select * from jobs order by maxof(salaryminus, salaryplus) limit 10;

maxof(Null,1000)应该是1000，

如何实现maxof?

推荐答案

如果您知道salaryplus总是大于salaryminus，那么您可以这样做

If you know that salaryplus will always be greater than salaryminus, then you can do

order by coalesce(salaryplus, salaryminus, 0)

coalesce将返回不为null的第一个值，或者，如果两个值均为null，则返回0(在此示例中).

coalesce will return the first value which is not null, or (in this example) 0, if both values are null.

否则，请执行以下操作:

Otherwise, do something like this:

order by greatest(ifnull(salaryminus,0), ifnull(salaryplus,0))

如果salaryminus和salaryplus为空，这会将它们都视为0，并按两者中的较大者排序.

This will treat both salaryminus and salaryplus as 0 if they are null, and will order by the larger of the two.

这篇关于如何在MySQL中最多可以为null的两列排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！