"ORDER BY'column_name'DESC",但是字母前的数字需要两个语句? [英] "ORDER BY 'column_name' DESC", but numbers before letters requires two statements?
问题描述
我想在数据库中选择数据并按字母顺序排序,但先是数字,然后是字母.
I want to select data in my database and order it alphabetically but numbers first and then letters.
我的桌子的样品:
watch_date
474 1442437 2181257 2015-12-20 Modern Family 3 13 Little Bo Bleep
475 1442437 2189485 2015-12-21 Modern Family 3 14 Me? Jealous?
476 1442437 2209159 2015-12-22 Modern Family 3 15 Aunt Mommy
477 1442437 2237415 2015-12-22 Modern Family 3 16 Virgin Territory
478 1442437 2244103 2015-12-22 Modern Family 3 17 Leap Day
479 1442437 2239817 2015-12-24 Modern Family 3 18 Send Out the Clowns
480 1442437 2305713 2015-12-24 Modern Family 3 19 Election Day
481 1442437 2305715 2016-01-02 Modern Family 3 20 The Last Walt
482 1442437 2247703 2016-01-03 Modern Family 3 21 Planes, Trains and Cars
483 1553656 2656658 unknown Under the Dome 1 1 Pilot
484 1553656 2779640 unknown Under the Dome 1 2 The Fire
485 1553656 2821206 unknown Under the Dome 1 3 Manhunt
我想使用第四列(带有日期和文字"unknown"(称为"watch_date")的列)进行排序
I want to order it using the fourth column (the column with dates and the text 'unknown' (it's called 'watch_date'))
此列中的数据是文本unknown
或格式为YYYY-MM-DD
The data in this column is either the text unknown
or a date with the format YYYY-MM-DD
为此,我使用以下查询:
To do this I use the following query:
SELECT * FROM `watched_episodes` ORDER BY `watch_date` + 0 DESC, `watch_date` DESC
我认为+ 0
部分为所有结果添加了0,但是当我不添加第二个ORDER BY语句时:
I thought the + 0
part added a 0 to all results, yet when I do not add the second ORDER BY statement:
`watch_date` DESC
它确实将数字(日期)放在字母的前面,但似乎根本没有对日期进行排序.
it does put the numbers (dates) before the letters but it does not seem to sort the dates at all.
推荐答案
嗯,您的查询根本是错误的.由于使用单引号,因此应该给出错误.我假设你打算:
Hmmm, your query is simply wrong. It should be giving an error because of the use of single quotes. I'll assume you intend:
SELECT *
FROM watched_episodes
ORDER BY watch_date + 0 DESC, watch_date DESC;
这似乎是一种奇怪的方法.如果值始终为'Unknown'
或YYYY-MM-DD
格式的字符串,我会选择:
This seems like a strange method. If the values is always 'Unknown'
or a string in YYYY-MM-DD
format, I would go for:
ORDER BY (watch_date = 'unknown'), -- USE `DESC` to put `unknown` first
watch_date DESC
这篇关于"ORDER BY'column_name'DESC",但是字母前的数字需要两个语句?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!