Как разделить строки запятыми в порядке возрастания с помощью STRING_AGG ()

Моя таблица выглядит так

Color  Order 
------------
Red    49
Blue   32
Green  80
Green  30
Blue   93
Blue   77
Red    20
Green  54
Red    59
Red    42
Red    81
Green  35
Blue   91

Мой запрос

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color

Когда я группирую по цвету и агрегату, я получаю несортированные заказы

Что-то вроде этого

Color  Count  AggOrder
------------------------------
Red    5      49,20,59,42,81
Blue   4      32,93,77,91
Green  4      80,30,54,35

Проблема : AggOrder не заказан 49,20,59,42,81

Я хочу заказать это

так что конечный результат

Color  Count  AggOrder
------------------------------
Red    5      20,42,49,59,81
Blue   4      32,77,91,93
Green  4      30,35,54,80

Я пробовал этот запрос

SELECT Color, Count(*) AS Count, STRING_AGG(Order,',') AS AggOrder
FROM MyTable
GROUP BY Color
ORDER BY Order

Но это дает ошибку.

Есть идеи, как это исправить?

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
63
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Вы можете использовать синтаксис within group

SELECT Color
    , Count(*) AS Count
    , STRING_AGG([Order],',') WITHIN GROUP (ORDER BY [Order]) AS AggOrder
FROM MyTable
GROUP BY Color

Добавьте предложение ORDER BY к вашему обращению к STRING_AGG:

SELECT
    Color,
    COUNT(*) AS Count,
    STRING_AGG([Order], ',') WITHIN GROUP (ORDER BY CAST([Order] AS INT)) AS AggOrder
FROM MyTable
GROUP BY Color;

Два комментария к колонке Order. Во-первых, ORDER - это зарезервированное ключевое слово SQL Server, и запрос, который вы вставили в свой вопрос, даже не будет работать как заданный, потому что ORDER при использовании в качестве имени объекта базы данных необходимо экранировать. Вам следует избегать именования столбцов, таблиц и т. д. С помощью ключевых слов. Во-вторых, предполагая, что столбец Order является текстовым, если вы хотите упорядочить его, используя его как число, вы должны сначала преобразовать его в целое число.

Я думаю (или надеюсь), что более вероятно, что столбец [Order] - это int, и поэтому он должен читать STRING_AGG(CAST([Order] AS varchar(10))) WITHIN....

Charlieface 30.03.2021 06:33

@Charliefacepalm Да ... Я тоже об этом подумал, возможно, ты прав.

Tim Biegeleisen 30.03.2021 06:34

Для SQL Server 2017 или более поздней версии вы уже получили свой ответ. Но если SQL Server старше 2017 года, вы можете использовать stuff () с XML PATH FOR (), чтобы получить желаемый результат:

Поскольку вы используете SQL Server 2017 или более поздней версии, пожалуйста, не используйте это решение, а используйте решение с string_agg (), поскольку оно намного быстрее и проще в реализации.

Схема:

 create table MyTable (color varchar(10), [order] int);
 insert into MyTable  values('Red',    49);
 insert into MyTable  values('Blue',   32);
 insert into MyTable  values('Green',  80);
 insert into MyTable  values('Green',  30);
 insert into MyTable  values('Blue',   93);
 insert into MyTable  values('Blue',   77);
 insert into MyTable  values('Red',    20);
 insert into MyTable  values('Green',  54);
 insert into MyTable  values('Red',    59);
 insert into MyTable  values('Red',    42);
 insert into MyTable  values('Red',    81);
 insert into MyTable  values('Green',  35);
 insert into MyTable  values('Blue',   91);

Запрос:

 SELECT color,count(*) [Count],
    STUFF((SELECT ',' + COALESCE(LTRIM(RTRIM([order])), '') 
    FROM MyTable mt 
        WHERE mt.color = m.color
        order by [order]
    FOR XML PATH('') ), 1, 1, ''
    ) as AggOrder
 FROM MyTable m
 group by color
 

Выход:

цветСчитатьAggOrder
Синий432,77,91,93
Зеленый430,35,54,80
красный520,42,49,59,81

db <> рабочий пример здесь

Другие вопросы по теме