按日期排序(varchar)? [英] Order by date (varchar)?

问题描述

我想按日期订购.

例如

table_date
February 2011
January 2011
December 2010

我已经尝试过:

SELECT distinct(table_date) FROM tables ORDER BY table_date DESC

不起作用.

我反而得到了:

January 2011
February 2011
December 2010

你能帮我吗?

推荐答案

如果要按日期排序，请将其存储为日期而不是字符串.除非日期字符串的格式为yyyy-mm-dd，它不会按照您想要的顺序排序.

If you want to order by date, store it as a date, not a string. Unless your date string is of the form yyyy-mm-dd, it will not sort as you want it.

数据库可以按原样进行足够的工作，而无需人们加倍努力，因此，您应该尽一切努力避免我喜欢的 SQL体操.

Databases are hard enough work as-is, without people making it harder, and you should be striving as much as possible to avoid what I like to call SQL gymnastics.

将其存储为日期，然后(如果必须)使用日期函数以February 2011的形式获取它.

Store it as a date then, if you must, use date functions to get it in the form February 2011.

用这种方法比您尝试做的要容易得多.

It'll be a lot easier going that way than what you're trying to do.

即使由于代码限制而不能更改任何当前列，也可以始终将另一列添加到数据库中，例如TABLE_DATE_AS_DATE并放入插入/更新触发器以进行填充它基于TABLE-DATE.

Even if you can't change any of the current columns due to code restrictions, you can always add another column to the database like TABLE_DATE_AS_DATE and put in an insert/update trigger to populate it based on TABLE-DATE.

然后就做:

update table x set table_date = table_date

或类似的东西，触发所有行的触发器.

or something similar, to fire the trigger for all rows.

然后，您的查询仍然可以在table_date处进行，但使用table_date_as_date进行排序.当然这是一个麻烦，但是在过去必须强制代码无法更改的情况下，我不得不使用过去的技巧，因此我们不得不求助于DBMS技巧.

Then, your query can still get at table_date but use table_date_as_date for ordering. That's a kludge of course but I've had to use tricks like that in the past when it was imperative the code could not change, so we had to resort to DBMS trickery.

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