id name
1 aa
2 aa
3 aa
4 d
5 c
6 aa
7 aa
8 e
9 f
10 g
使用sql从以上数据变为id name
7 aa|aa|aa|aa|aa
4 d
5 c
8 e
.9 f
10 g`
以下是建表mysql语句
-- 创建表
CREATE TABLE A727 (
id INT,
name VARCHAR(255)
);
-- 插入数据
INSERT INTO A727 (id, name)
VALUES
(1, 'aa'),
(2, 'aa'),
(3, 'aa'),
(4, 'd'),
(5, 'c'),
(6, 'aa'),
(7, 'aa'),
(8, 'e'),
(9, 'f'),
(10, 'g');
SELECT MAX(id) AS id, GROUP_CONCAT(name SEPARATOR '|') AS name
FROM A727
GROUP BY name;
id name
1 aa
2 aa
3 aa
4 d
5 c
6 aa
7 aa
8 e
9 f
10 g
使用mysql从以上数据变为id name
3 aa|aa|aa
4 d
5 c
7 aa|aa
8 e
9 f
10 g
WITH marked AS (
SELECT id, name,
CASE
WHEN LAG(name) OVER (ORDER BY id) != name THEN 1
ELSE 0
END as is_new_group
FROM a727
),
grouped AS (
SELECT id, name,
SUM(is_new_group) OVER (ORDER BY id) as group_id
FROM marked
)
SELECT MAX(id) as id, GROUP_CONCAT(name SEPARATOR '|') as name
FROM grouped
GROUP BY group_id;
这个查询使用了两个公共表表达式(CTE):marked和grouped。
首先,marked表使用窗口函数LAG()来比较每个记录的name与前一个记录的name。如果两者不同,则将is_new_group设置为1,否则设置为0。这样可以标记出每个新的名字组。
然后,grouped表使用窗口函数SUM()来计算每个记录的group_id。group_id的值是从表的开始到当前记录的is_new_group的总和。这样可以将连续的相同名字分配到同一个组。
最后,使用GROUP_CONCAT函数将同一个组的所有记录合并为一条记录,名字之间用’|'分隔。并且使用MAX函数选择每个组的最大id作为结果。
SELECT MAX(id) as id, GROUP_CONCAT(name SEPARATOR '|') as name
FROM (
SELECT id, name,
@rn := IF(@prev = name, @rn, @rn + 1) as rn,
@prev := name
FROM a727
JOIN (SELECT @rn := 0, @prev := NULL) as vars
ORDER BY id
) t
GROUP BY rn;
. SELECT MAX(id) as id, GROUP_CONCAT(name SEPARATOR ‘|’) as name: 这是查询的主体部分。它选择了两个列:id和name。MAX(id) as id表示选择每个连续相同名字的最大id作为结果的id列。GROUP_CONCAT(name SEPARATOR ‘|’) as name表示将同一个连续相同名字的所有记录的name列合并为一条记录,名字之间用’|'分隔。
-
FROM (SELECT id, name, @rn := IF(@prev = name, @rn, @rn + 1) as rn, @prev := name FROM a727 JOIN (SELECT @rn := 0, @prev := NULL) as vars ORDER BY id) t: 这是一个子查询,它从表a727中选择了id和name列,并且使用变量@rn和@prev来计算每个连续相同名字的记录的编号rn。@rn变量用于记录每个名字的出现次数,@prev变量用于记录前一个记录的名字。通过IF(@prev = name, @rn, @rn + 1)条件语句,如果当前记录的名字与前一个记录的名字相同,则rn保持不变,否则递增1。最后,使用ORDER BY id对结果进行排序。
-
GROUP BY rn: 这是对子查询结果的分组操作。它将具有相同rn值的记录分为一组。