sql—mysql数据库中基于大多数行的赋值顺序不为空

ufj5ltwl 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(400)

我正在生成变量的所有组合，并将其插入临时表

select *
from (select 33 as age union all select null) a cross join
     (select 44 as totaldd union all select null) t cross join
     (select 30 as timeperiod union all select null) d cross join
     (select CURDATE() as StartDateTime union all select null) s

结果如下：

我要基于大多数行的顺序不是空的像

sql mysql window-functions Database

来源：https://stackoverflow.com/questions/62993171/assign-order-based-on-most-rows-are-not-null-in-mysql-database

1条答案

按热度按时间

xmakbtuz1#

如果您运行的是mysql 8.0，那么可以使用 dense_rank() 和条件表达式：

select 
    a.age, 
    t.totaldd, 
    d.timeperiod, 
    s.startdatetime,
    dense_rank() over(order by 
          (a.age is null) 
        + (t.totaldd is null) 
        + (d.timeperiod is null) 
        + (s.startdatetime is null)
    ) as rn
from (select 33 as age union all select null) a cross join
     (select 44 as totaldd union all select null) t cross join
     (select 30 as timeperiod union all select null) d cross join
     (select CURDATE() as startdatetime union all select null) s

实际上，您甚至可以不用window函数，只使用条件表达式：

select 
    a.age, 
    t.totaldd, 
    d.timeperiod, 
    s.startdatetime,
    1 + (a.age is null) 
        + (t.totaldd is null) 
        + (d.timeperiod is null) 
        + (s.startdatetime is null) as rn
from (select 33 as age union all select null) a cross join
     (select 44 as totaldd union all select null) t cross join
     (select 30 as timeperiod union all select null) d cross join
     (select CURDATE() as startdatetime union all select null) s

赞(0）回复(0）举报 2021-07-24

我来回答

sql—mysql数据库中基于大多数行的赋值顺序不为空

1条答案

相关问题

热门标签

最新问答