按案例排序 [英] ORDER BY CASE

问题描述

这是我的查询=


" SELECT i.itemid，title，SortKey from Items AS i JOIN Links AS L ON

（i。 ItemID = L.ItemID）WHERE L.instructorID =''12232''和courseID =''12''和

type =''音频''按CASE排序WHEN Sortkey不为null然后1否则0结束


我的SortKey可以为NULL。这是我得到的输出：

（||表示sortkey列）


37542磁带1 ||

37544磁带2 ||

37819 Symphony1 ||

37820 Symphony2 ||

37821 Symphony3 ||

37828 Symphony ||

60962测试||

61570新测试记录|非洲|

61572测试3 |非洲1 |

63186字符串音乐|巴西|


我想使用Sortkey，它不是null。期望输出：


61570新测试记录|非洲|

61572测试3 |非洲1 |

63186弦乐音乐|巴西|

37542磁带1 ||

37544磁带2 ||
37819 Symphony1 ||

37820 Symphony2 ||

37821 Symphony3 ||

37828 Symphony ||

60962测试||

this is my query=

"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID=''12232'' AND courseID=''12'' AND
type=''Audio'' order by CASE WHEN Sortkey is not null then 1 else 0 end"

My SortKey can be NULL. Here''s the output I am getting:
(the || is to denote sortkey column)

37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|

I want use Sortkey when it is not null. desired output:

61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||

推荐答案

Sharif Islam写道：

Sharif Islam wrote:

这是我的查询=


" SELECT i .itemid，title，SortKey from Items AS i JOIN Links AS L ON

（i.ItemID = L.ItemID）WHERE L.instructorID =''12232''SOUTHID =''12''并且

type =''音频''按CASE WHEN排序，而排序键不为空，则为1，否则为0结束

this is my query=

"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID=''12232'' AND courseID=''12'' AND
type=''Audio'' order by CASE WHEN Sortkey is not null then 1 else 0 end"

ok我认为想通了，我最后需要一个desc。

SELECT i.itemid，title，来自Items AS的SortKey我加入链接AS L ON

（i.ItemID = L.ItemID）WHERE L.instructorID =''12232''AND courseID ='''12''和

type ='''音频''按CASE排序当排序键不为空，然后是1，否则0结束desc"

让我知道是否有更好的方法可以做到这一点。

ok I think figured it out, i needed a desc at the end.
SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID=''12232'' AND courseID=''12'' AND
type=''Audio'' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"
let me know if there''s a better way to do this.

>

我的SortKey可以为NULL。这是我得到的输出：

（||表示sortkey列）


37542磁带1 ||

37544磁带2 ||

37819 Symphony1 ||

37820 Symphony2 ||

37821 Symphony3 ||

37828 Symphony ||

60962测试||

61570新测试记录|非洲|

61572测试3 |非洲1 |

63186字符串音乐|巴西|


我想使用Sortkey，它不是null。期望输出：


61570新测试记录|非洲|

61572测试3 |非洲1 |

63186弦乐音乐|巴西|

37542磁带1 ||

37544磁带2 ||
37819 Symphony1 ||

37820 Symphony2 ||

37821 Symphony3 ||

37828 Symphony ||

60962测试||

>
My SortKey can be NULL. Here''s the output I am getting:
(the || is to denote sortkey column)

37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||
61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|

I want use Sortkey when it is not null. desired output:

61570 New Test Record |Africa|
61572 Test 3 |Africa 1|
63186 Music for Strings |Brazil|
37542 Tape 1 ||
37544 Tape 2 ||
37819 Symphony1 ||
37820 Symphony2 ||
37821 Symphony3 ||
37828 Symphony ||
60962 Test ||

Sharif Islam写道：

Sharif Islam wrote:

>" ; SELECT i.itemid，title，SortKey from Items AS i JOIN Links AS L ON
（i.ItemID = L.ItemID）WHERE L.instructorID =''12232''AND courseID =''12''AND
类型='''音频''按CASE WHEN排序排序键不为空，然后是1，否则0结束

>"SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID=''12232'' AND courseID=''12'' AND
type=''Audio'' order by CASE WHEN Sortkey is not null then 1 else 0 end"

>我希望在它不为null时使用Sortkey。期望的输出：

>I want use Sortkey when it is not null. desired output:

ok我认为想通了，我最后需要一个desc。

ok I think figured it out, i needed a desc at the end.

SELECT i.itemid，title，SortKey from Items AS i JOIN Links AS L ON

（i.ItemID = L.ItemID）WHERE L.instructorID =''12232''和courseID =''12''

type =''音频''按CASE排序WHEN Sortkey不为null然后是1否则0结束desc

SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON
(i.ItemID=L.ItemID) WHERE L.instructorID=''12232'' AND courseID=''12'' AND
type=''Audio'' order by CASE WHEN Sortkey is not null then 1 else 0 end desc"

让我知道是否有更好的方法来做到这一点。

let me know if there''s a better way to do this.

这样可以确保查询返回的行为非空的SortKey

优先，行为零的SortKey为秒。


确实/不确保查询将返回具有SortKey的行

非洲首先，使用SortKeyBrazil的行。第二。这次以这种方式工作了，但是无法保证它每次都会以这种方式工作。为了得到这个保证，请执行以下操作：


订购

CASE当SortKey不为null然后其他2结束，

coalesce（SortKey，''''）

This ensures that the query will return rows with non-null SortKey
first, rows with null SortKey second.

It does /not/ ensure that the query will return rows with SortKey
"Africa" first, rows with SortKey "Brazil" second. It happened
to work that way this time, but there are no guarantees that it
will work that way every time. To get that guarantee, do this:

order by
CASE WHEN SortKey is not null then 1 else 2 end,
coalesce(SortKey,'''')

良好的软件工程和SQL的快速课程：将排序键

放入一个列中你的结果SELECT列表并给它一个名字。


1）好的SQL：在ORDER BY子句中使用表达式是一个

的专有功能。在SQL Server而不是标准SQL中。 ORDER BY

caluse是光标的alawyas的一部分，而不是SELECT语句，因为

DSELECT生成一个按定义没有顺序的表。


2）好的SE：始终显示您用于将结果集排序到架构的下一个

层的内容。他们可能需要使用它。嘿，最终的

用户可能会在他的显示屏上找到更容易找到的数据，如果有的话！

A quick lesson in good software engineering and SQL: Put the sort key
into a column in your result SELECT list and give it a name.

1) Good SQL: Using an expression in an ORDER BY clause is a
proprietary "feature" in SQL Server and not Standard SQL. The ORDER BY
caluse is alawyas part of a cursor, not a SELECT statement, since
DSELECT produces a table which has no order by definition.

2) Good S.E.:Always show what you used to sort a result set to the next
tier of the architecture. They might need to use it. Hey, the final
user might fidn data easier to find on his display if it was there!

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