英文:
How can I perform this result table by mysql from my input table
问题
You can use the following MySQL statement to create an output table similar to the one in the input table:
CREATE TABLE OutputTable AS
SELECT
ID,
Name,
SUM(Value1) AS Total_Value1,
SUM(Value2) AS Total_Value2
FROM InputTable
GROUP BY ID, Name;
This statement will create a table called OutputTable with columns ID, Name, Total_Value1, and Total_Value2, where Total_Value1 and Total_Value2 are the sums of Value1 and Value2 respectively, grouped by ID and Name.
英文:
I have this input table:
With which MySQL statement can I create such an output table?
答案1
得分: 1
以下是您要翻译的内容：
SELECT
ID,
CONCAT('D', ROW_NUMBER() OVER (ORDER BY ID_LIST)) AS DUB,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1), ';', -1)) AS KEY
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
JOIN
(
SELECT
SUBSTRING_INDEX(ID_LIST, ';', n.digit+1) AS ID,
SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1) AS Mykey_LIST
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
CROSS JOIN
(SELECT @row_number:=0) r
JOIN
(SELECT @row_number:=@row_number+1 AS row_number, ID_LIST, Mykey_LIST
FROM your_table
ORDER BY ID_LIST) t
ON
n.digit+1 <= LENGTH(ID_LIST) - LENGTH(REPLACE(ID_LIST, ';', '')) + 1
) t
ON
t.ID != ''
ORDER BY
ID;
英文:
You can try the below query
SELECT
ID,
CONCAT('D', ROW_NUMBER() OVER (ORDER BY ID_LIST)) AS DUB,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1), ';', -1)) AS KEY
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
JOIN
(
SELECT
SUBSTRING_INDEX(ID_LIST, ';', n.digit+1) AS ID,
SUBSTRING_INDEX(Mykey_LIST, ';', n.digit+1) AS Mykey_LIST
FROM
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
CROSS JOIN
(SELECT @row_number:=0) r
JOIN
(SELECT @row_number:=@row_number+1 AS row_number, ID_LIST, Mykey_LIST
FROM your_table
ORDER BY ID_LIST) t
ON
n.digit+1 <= LENGTH(ID_LIST) - LENGTH(REPLACE(ID_LIST, ';', '')) + 1
) t
ON
t.ID != ''
ORDER BY
ID;
答案2
得分: 1
将逗号分隔的字符串拆分为行，需要在字符串周围添加方括号（[]），使其成为JSON数组。然后使用 JSON_TABLE 将其转换为表格：
我们需要创建两个公共表达式（CTEs），一个用于IDs，另一个用于KEYs，然后将它们连接在一起以获得期望的输出：
with cte_id_list as (
select ID_LIST, j.ID, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.ID_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
ID int PATH '$'
)
) j
),
cte_key_list as (
select ID_LIST, j.`KEY`, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.Mykey_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
`KEY` text PATH '$'
)
) j
)
select ci.ID, concat('D', dense_rank() over (order by ci.ID_LIST)) as DUB, ck.`KEY`
from cte_id_list ci
inner join cte_key_list ck on ck.ID_LIST = ci.ID_LIST and ci.rn = ck.rn
英文:
To Split comma separated string into rows concatenate square brackets ([]) around your string to make it into a JSON array. Then use JSON_TABLE to convert it into a table :
We need to create two CTEs one for IDs and then other for KEYs the join them together to get the expected output :
with cte_id_list as (
select ID_LIST, j.ID, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.ID_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
ID int PATH '$'
)
) j
),
cte_key_list as (
select ID_LIST, j.`KEY`, row_number() over (partition by ID_LIST) as rn
from mytable t
cross join JSON_TABLE(
CONCAT('["', REPLACE(t.Mykey_LIST, ';', '","'), '"]'),
'$[*]'
COLUMNS (
`KEY` text PATH '$'
)
) j
)
select ci.ID, concat('D', dense_rank() over (order by ci.ID_LIST)) as DUB, ck.`KEY`
from cte_id_list ci
inner join cte_key_list ck on ck.ID_LIST = ci.ID_LIST and ci.rn = ck.rn
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论