拆分SQL查询中的字段并按它们排序 [英] Splitting fields in sql query and sorting by them

问题描述

我有一个包含合并日期的字段，行2012-02-03-2012-02-05，其中第一个日期是开始"日期，第二个日期是结束"日期.

I have a field containg a combined date, something line 2012-02-03 - 2012-02-05 where the first date is the "from" date, and second is the "to" date.

有没有一种方法可以拆分这两个日期，然后按起始"日期排序?

Is there a way that I can split these two dates and then order by the "from" date ?

我知道最好的办法是在这两个日期中拥有两个不同的字段，但是自从我以这种方式开始执行此操作以来，就进行了大量重新编码以将它们分开.

I know the best thing would be to have two different fields for these two dates, but since I began doing it this way there is alot of recoding to seperate them.

推荐答案

您可以使用

You can use SUBSTRING_INDEX for this.

假设您的日期之间有'-'(即空格，连字符，空格)，您可以执行以下操作:

Assuming your dates have ' - ' between them (i.e. space, hypen, space), you can do something like:

SELECT SUBSTRING_INDEX(datefield,' - ',1) as fromDate,
       SUBSTRING_INDEX(datefield,' - ',-1) as toDate,
FROM ..
ORDER BY DATE(fromDate)

SUBSTRING_INDEX(string，delimiter，count)返回直到 count 次出现为止的所有 string 分隔符.如果 delimiter 为负，则从右边开始计数，并从字符串的末尾返回子字符串.

SUBSTRING_INDEX(string,delimiter,count) returns all of string up to (not including) the countth occurence of delimiter. If delimiter is negative it counts from the right and returns the substring from the end of the string instead.

我使用 DATE(fromDate)将 fromDate 从字符串转换为MySQL Date，以便它以类似日期的方式进行排序.

I used DATE(fromDate) to convert fromDate from a string to a MySQL Date so it sorts in a date-like fashion.

这篇关于拆分SQL查询中的字段并按它们排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！