gpt4 book ai didi

MySQL 像交叉表一样翻转数据

转载 作者:行者123 更新时间:2023-11-30 01:11:30 25 4
gpt4 key购买 nike

我有以下地址数据:

ID     Address
---------------
1 123 Riverside Drive
1 Pleasantvile
1 Some Country
2 96 Another Street
2 Europe

是否有 SQL hack 或简单的方法来根据 ID 翻转此数据,以便我将以下结果分散到多个地址字段中:

ID     Address1             Address2        Address3
----------------------------------------------------------
1 123 Riverside Drive Pleasantvile Some Country
2 96 Another Street Europe

谢谢。

最佳答案

如果您事先不知道每个 id 的地址数量

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN rnum = ', rnum,
' THEN address END) `address', rnum, '`'))
INTO @sql
FROM
(
SELECT id, address, @n := IF(@g = id, @n + 1, 1) rnum, @g := id
FROM table1 CROSS JOIN (SELECT @n := 0, @g := NULL) i
ORDER BY id, address
) q;

SET @sql = CONCAT('SELECT id,', @sql,
' FROM
(
SELECT id, address, @n := IF(@g = id, @n + 1, 1) rnum, @g := id
FROM table1 CROSS JOIN (SELECT @n := 0, @g := NULL) i
ORDER BY id, address
) q
GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这里是SQLFiddle 演示

关于MySQL 像交叉表一样翻转数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19443843/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com