本文介绍了按案例排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是我的查询=


" 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 ||

推荐答案



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

优先,行为零的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,'''')



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

10-16 09:11