取决于列值的条件 ORDER BY [英] Conditional ORDER BY depending on column values

问题描述

我需要编写一个执行此操作的查询:

I need to write a query that does this:

SELECT TOP 1 
FROM a list of tables (Joins, etc)
ORDER BY Column X, Column Y, Column Z

如果 ColumnX 是 NOT NULL，那么此刻，我重新选择，使用略有不同的 ORDER BY.

If ColumnX is NOT NULL, then at the moment, I reselect, using a slightly different ORDER BY.

所以，我做了两次相同的查询.如果第一个在某个列中有 NULL，我会从我的过程中返回该行.但是，如果值不是 NULL - 我必须做另一个相同的选择，除了按不同的一两列排序.

So, I do the same query, twice. If the first one has a NULL in a certain column, I return that row from my procedure. However, if the value isn't NULL - I have to do another identical select, except, order by a different column or two.

我现在要做的是第一次将其选入临时表.然后检查列的值.如果没问题，返回临时表，否则，重做选择并返回结果集.

What I do now is select it into a temp table the first time. Then check the value of the column. If it's OK, return the temp table, else, redo the select and return that result set.

更多详情:

在英语中，我向数据库提出的问题:

In english, the question I am asking the database:

返回我所有出庭结果(通过索引外键).我预计大约有 1000 行.按出现日期(列，未编入索引，可为空)排序，最后出现在最前面.检查importId".如果前 1 行的导入 ID 不为 NULL，那么我们需要运行相同的查询 - 但这次，按导入 ID(最后一个在前)排序，并返回该行.否则，只需返回原始查询的前 1 行.

Return my all the results for certain court appearance (By indexed foreign key). I expect around 1000 rows. Order it by the date of the appearance (column, not indexed, nullable), last appearance first. Check an 'importId'. If the import ID is not NULL for that top 1 row, then we need to run the same query - but this time, order by the Import ID (Last one first), and return that row. Or else, just return the top 1 row from the original query.

推荐答案

我认为最好的方法是在单个查询中使用 CASE 语句...

I'd say the BEST way to do this is in a single query is a CASE statement...

SELECT TOP 1 FROM ... ORDER BY 
    (CASE WHEN column1 IS NULL THEN column2 ELSE column1 END) 

这篇关于取决于列值的条件 ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！