英文:
Is there a way or a function to sort individual string elements in sql?
问题
以下是翻译好的部分：
让我们假设我有以下表格：
| 元素 | 分数 |
|---|---|
| ABC | 50 |
| BAC | 10 |
| CBA | 30 |
| XYZ | 115 |
| ZXY | 25 |
SQL 应该提供以下输出。它应该对元素进行单独排序，然后相加值：
| 元素 | 分数 |
|---|---|
| ABC | 90 |
| XYZ | 140 |
我已经使用以下代码来对元素进行排序：然而，这看起来不是很优化。如果有一个函数可以执行此操作，将会更好：
SELECT
element,
CONCAT_WS('', sorted_chars) AS sorted_input
FROM
(SELECT
element,
sort_array(collect_list(char), true) AS sorted_chars
FROM
(SELECT
element,
explode(split(element, '')) AS char
FROM
table) t
GROUP BY
element) t
英文:
Let's say I have the below table:
| element | score |
|---|---|
| ABC | 50 |
| BAC | 10 |
| CBA | 30 |
| XYZ | 115 |
| ZXY | 25 |
The SQL should give the following output. It should sort the element individually and then add up the values
| element | score |
|---|---|
| ABC | 90 |
| XYZ | 140 |
I have used the below code to sort the elements: however this doesn't look very optimised. It would be better if there is a function to do this:
SELECT
element,
CONCAT_WS('', sorted_chars) AS sorted_input
FROM
(SELECT
element,
sort_array(collect_list(char), true) AS sorted_chars
FROM
(SELECT
element,
explode(split(element, '')) AS char
FROM
table) t
GROUP BY
element) t
答案1
得分: 2
以下是你要的翻译内容：
A solution (for MySQL), for MSSQL see note below, in steps:
首先将ABC（或BAC）拆分为单个字母：
-- split the strings (currently fixed to max 3 positions...)
SELECT
element,
score,
x,
substring(element, x, 1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
输出：
| element | score | x | Y |
|---|---|---|---|
| ABC | 50 | 3 | C |
| ABC | 50 | 2 | B |
| ABC | 50 | 1 | A |
| BAC | 10 | 3 | C |
| BAC | 10 | 2 | A |
| 等等。 |
然后创建element_new
-- create element_new
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element, score;
最后一步，在element_new上进行分组：
SELECT
element_new,
sum(score) as score
FROM (
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element, score
) z
GROUP BY element_new;
输出：
| element_new | score |
|---|---|
| ABC | 90 |
| XYZ | 140 |
参见：DBFIDDLE
编辑：
对于MSSQL，只需要将GROUP_CONCAT()更改为STRING_AGG()。参见：DBFIDDLE for MSSQL
英文:
A solution (for MySQL), for MSSQL see note below, in steps:
First split the ABC (or BAC) to individual letters:
-- split the strings (currently fixed to max 3 positions...)
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
output:
| element | score | x | Y |
|---|---|---|---|
| ABC | 50 | 3 | C |
| ABC | 50 | 2 | B |
| ABC | 50 | 1 | A |
| BAC | 10 | 3 | C |
| BAC | 10 | 2 | A |
| etc. |
Then create element_new
-- create element_new
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score;
Last step, group on element_new:
SELECT
element_new,
sum(score) as score
FROM (
SELECT
element,
score,
GROUP_CONCAT(y order by y SEPARATOR '') as element_new
FROM (
SELECT
element,
score,
x,
substring(element,x,1) as Y
FROM mytable
CROSS JOIN (SELECT 1 as x union all select 2 union all select 3) x
) Y
GROUP BY element,score
) z
GROUP BY element_new;
output:
| element_new | score |
|---|---|
| ABC | 90 |
| XYZ | 140 |
see: DBFIDDLE
EDIT:
For MSSQL only the GROUP_CONCAT() need to be changed to STRING_AGG(). see: DBFIDDLE for MSSQL
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论