如何按结合 COALESCE 的 2 列排序? [英] How to order by 2 columns combining COALESCE?

问题描述

我有一个关于订购 SQL 表的问题.而且我在堆栈或谷歌上找不到解决方案.我的分数"表如下所示:

I have a question about ordering a SQL table. And I can't find a solution on stack or google. My table "Score" seems as follows:

Name    Total   Tries  Game1  Game2  Game3
------------------------------------------
Sam     65      61     10     31     24             
Tom             55     11            30
Jim     65      58     9      34     22 
Dan     62      52     10     30     22

注意:总计"列是 COUNT(Game1 + Game2 + Game3).

Note: "Total" column is COUNT(Game1 + Game2 + Game3).

如您所见，Tom 的 Total 记录为空，因为 Tom 没有玩 Game2.

As you can see the Total record of Tom is empty, because Tom didn't play Game2.

我想按如下方式订购我的餐桌(最高-最低优先级):

I want to order my table as follows (highest-lowest priority):

1. 空单元格(在表格底部)
2. 总计(ASC)
3. 尝试 (ASC)

所以我的表必须看起来像:

So my table has to look like:

Name    Total   Tries  Game1  Game2  Game3
------------------------------------------
Dan     62      52     10     30     22
Jim     65      58     9      34     22 
Sam     65      61     10     31     24             
Tom             55     11            30

我必须使用哪个 SQL 查询?

Which SQL query do I have to use?

这就是我目前所拥有的，但它不会按尝试"排序:

This is what I have on this moment, but it will not sort on "Tries":

SELECT name, Game1+Game2+Game3 AS Total, Game1, Game2, Game3, Tries
FROM Score 
ORDER BY CASE WHEN Tries or Game1 or Game2 or Game3 IS NULL THEN 0 ELSE 1 END DESC, 
         COALESCE(Game1,0) + COALESCE(Game2,1) + COALESCE(Game3,2) ASC

推荐答案

考虑到您在 Total 中有 NULL 而不是 empty 基于CASE 语句在您的 order by 中.试试这个Order by

Considering you have NULL instead of empty in Total based on the CASE statement in your order by. Try this Order by

ORDER BY COALESCE(Total,99999999999) ASC,Tries ASC

99999999999 将使 NULL 值排在最后，其他记录按升序排列

99999999999 will make the NULL values to sort at the end and the other records will be ordered in ascending order

或

ORDER BY case when Total is null then 0 else 1 end desc,Total ASC,Tries ASC

这篇关于如何按结合 COALESCE 的 2 列排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！