Как правильно отсортировать строку varchar с числовыми значениями на обоих концах?

Я строю Common Table Expression (CTE) в SQL Server 2008 использовать в PIVOT запрос.

У меня возникают трудности с сортировкой выходных данных должным образом, потому что есть числовые значения, которые помещают строковые данные в середину. Можно ли сделать это?

Это быстрый и грязный пример, реальный запрос будет стоить несколько лет.

Пример:

Declare @startdate as varchar(max);
Declare @enddate as varchar(max);
Set @startdate = cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-12-01';
Set @enddate = cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-03-15';

WITH DateRange(dt) AS
    (
        SELECT CONVERT(datetime, @startdate) dt
        UNION ALL
        SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT DISTINCT ',' + QUOTENAME((cast(DATEPART(yyyy, dt) as varchar(4)))+'-Week'+(cast(DATEPART(ww, dt) as varchar(2)))) FROM DateRange

Токовый выход:

,[2012-Week48]
,[2012-Week49]
,[2012-Week50]
,[2012-Week51]
,[2012-Week52]
,[2012-Week53]
,[2013-Week1]
,[2013-Week10]
,[2013-Week11]
,[2013-Week2]
,[2013-Week3]
,[2013-Week4]
,[2013-Week5]
,[2013-Week6]
,[2013-Week7]
,[2013-Week8]
,[2013-Week9]

Желаемый результат:

,[2012-Week48]
,[2012-Week49]
,[2012-Week50]
,[2012-Week51]
,[2012-Week52]
,[2012-Week53]
,[2013-Week1]
,[2013-Week2]
,[2013-Week3]
,[2013-Week4]
,[2013-Week5]
,[2013-Week6]
,[2013-Week7]
,[2013-Week8]
,[2013-Week9]
,[2013-Week10]
,[2013-Week11]

РЕДАКТИРОВАТЬ

Конечно, после того, как я отправил вопрос, мой мозг начал работать. Я изменил DATEADD добавить 1 неделю вместо 1 дня, а затем вынул DISTINCT в избранном и все заработало.

DECLARE @startdate AS VARCHAR(MAX);
DECLARE @enddate AS VARCHAR(MAX);
SET @startdate = CAST((DATEPART(yyyy, GetDate())-1) AS VARCHAR(4))+'-12-01';
SET @enddate = CAST((DATEPART(yyyy, GetDate())) AS VARCHAR(4))+'-03-15';

WITH DateRange(dt) AS
    (
            SELECT CONVERT(datetime, @startdate) dt
            UNION ALL
            SELECT DATEADD(ww,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT ',' + QUOTENAME((CAST(DATEPART(yyyy, dt) AS VARCHAR(4)))+'-Week'+(CAST(DATEPART(ww, dt) AS VARCHAR(2)))) FROM DateRange
Источник

6 ответов

Решение

Мне нужно было изменить DATEADD часть запроса и удалить DISTINCT, После того, как изменили порядок, отсортированы правильно самостоятельно

DECLARE @startdate AS VARCHAR(MAX);
DECLARE @enddate AS VARCHAR(MAX);
SET @startdate = CAST((DATEPART(yyyy, GetDate())-1) AS VARCHAR(4))+'-12-01';
SET @enddate = CAST((DATEPART(yyyy, GetDate())) AS VARCHAR(4))+'-03-15';

WITH DateRange(dt) AS
    (
            SELECT CONVERT(datetime, @startdate) dt
            UNION ALL
            SELECT DATEADD(ww,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT ',' + QUOTENAME((CAST(DATEPART(yyyy, dt) AS VARCHAR(4)))+'-Week'+(CAST(DATEPART(ww, dt) AS VARCHAR(2)))) FROM DateRange
Источник

Я не вижу пример кода SQL (этот сайт находится в черном списке, где я нахожусь).

Вот трюк для сортировки этих данных в правильном порядке - сначала использовать длину, а затем значения:

select col
from t
order by left(col, 6), len(col), col;
Источник

Рассматривали ли вы сортировку по двум временным столбцам (год в smallint и неделя в tinyint для экономии места… или напрямую, используя целое число datepart, если для вас не проблема пробел и вы предпочитаете быстрый запуск), наряду с использованием "порядка по году", неделю"?

Если вы храните даты, используя более подходящий тип (то, что я предлагаю), то это станет:

WITH [Define the CTE expression name and column list]
AS
  (
    SELECT CAST(DATEPART(yyyy, dt) as smallint(4)) year, cast(DATEPART(ww, dt) as tinyint(2)) week, [your columns here]
    FROM DateRange WHERE dt < @enddate
  )
[Define the outer query referencing the CTE name]
ORDER BY year, week;
GO

Также обратите внимание, что строковые операции замедляют ваши запросы, поэтому избегайте их, когда это возможно!

Источник

Так как вы используете dt чтобы сгенерировать строку, вы должны отсортировать, используя части даты:

WITH DateRange(dt) ...
SELECT DISTINCT ',' + QUOTENAM...
ORDER BY DATEPART(yyyy, dt), DATEPART(ww, dt)
Источник

Мне нравится ответ Гордона, но если вы одержимы манипулированием текстом в вашем порядке:

ORDER BY     CAST(REPLACE(LEFT('[2012-Week48]',5),'[','')AS INT)
            ,CAST(REPLACE(RIGHT('[2012-Week48]',CHARINDEX('Week','[2012-Week48]')-4),']','') AS INT)
Источник

Вот еще один вариант преобразования начальной и конечной частей столбца в целое число.

SELECT *
FROM YourTable
ORDER BY CAST(SUBSTRING(yourcolumn,1,4) as int), 
  CAST(SUBSTRING(yourcolumn,CHARINDEX('Week',yourcolumn)+4,len(yourcolumn)) as int)

Это будет работать при условии, что формат данных всегда одинаков.

Источник
Другие вопросы по тегам