我正在处理一个TABLE,需要逻辑帮助。
请检查以下URL以获取表结构和示例数据。
http://sqlfiddle.com/#!2/2年12月6日
表架构:
CREATE TABLE test (
ID INTEGER,
NAME VARCHAR (50),
VALUE INTEGER
);
插入数据:
INSERT INTO test VALUES (1, 'A', 4);
INSERT INTO test VALUES (1, 'B', 5);
INSERT INTO test VALUES (1, 'C', 8);
INSERT INTO test VALUES (2, 'D', 9);
INSERT INTO test VALUES (2, 'E', 9);
INSERT INTO test VALUES (3, 'F', 9);
INSERT INTO test VALUES (3, 'G', 9);
INSERT INTO test VALUES (3, 'H', 9);
INSERT INTO test VALUES (3, 'I', 9);
查询:
SELECT ID, GROUP_CONCAT(NAME) AS CODE
FROM test
GROUP BY ID;
输出:
ID CODE
1 A,B,C
2 D,E
3 F,G,H,I
预期输出:
ID CODE CODE CODE CODE
1 A B C NULL
2 D E NULL NULL
3 F G H I
正如您所看到的,查询的输出具有带逗号的concat。目前,我们正在使用PHP进行字符串concat,它在显示时进行拆分!!
有没有其他方法可以拆分RESULT并在列和同一ROW中显示每个值?结果是一样的吗?
注:代码可能因世界其他地区而异。
如果您知道GROUP_CONCT条目的数量(我的意思是,在ID=1的情况下组合3个字段,在ID为2的情况下合并2个字段等),那么就有一种肮脏的方法。
SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 1), ',', -1) AS CODE1,
If( length(GROUP_CONCAT(NAME)) - length(replace(GROUP_CONCAT(NAME), ',', ''))>1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 2), ',', -1) ,NULL)
as CODE2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 3), ',', -1) AS CODE3
FROM test
GROUP BY ID;
输出:
ID CODE1 CODE2 CODE3
1 A B C
2 D (null) E
3 F G H
上面的查询假设您是GROUP_CONCAT-ing 3个字段。如果您正在动态生成查询,您可以尝试一下。SQLFIDDLE
编辑:注:代码可能因世界其他地区而异(忽略此项)
在同事的帮助下,我们找到了解决这个问题的方法。希望有人可能需要它……如果有人能让它变得更简单,那就太好了。
BEGIN
SET @v=0;
SET @v1=0;
SELECT tmp.cnt INTO @v
FROM
(SELECT Id,
count(ID) AS cnt,
GROUP_CONCAT(name)
FROM test
GROUP BY id) tmp
ORDER BY tmp.cnt DESC LIMIT 1;
SET @str=' ';
WHILE(@v>@v1) DO
SET @v1=@v1+1;
IF(@str='') THEN
SET @str=CONCAT(@str,'ID, REPLACE(SUBSTRING(SUBSTRING_INDEX(GROUP_CONCAT(NAME), '','',', @v1,'),LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',', @v1,'-1)) + 1),'','','''') AS Code' ,@v1);
ELSE
SET @str= CONCAT(@str,',REPLACE(SUBSTRING(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',', @v1,'),LENGTH(SUBSTRING_INDEX(GROUP_CONCAT(NAME),'','',' , @v1,' -1)) + 1),'','','''') AS Code',@v1);
END IF;
END WHILE;
SET @str=CONCAT('SELECT ' , @str, ' FROM test GROUP BY ID');
PREPARE MYSQLQUERY
FROM @str;
EXECUTE MYSQLQUERY;
DEALLOCATE PREPARE MYSQLQUERY;
END
将其拆分为列的唯一方法意味着您提前知道要拆分的值(例如:一列用于与A, D, F
匹配的代码,另一列用于B, E, G
,依此类推)。事实似乎并非如此。
防弹的方法是不按PHP分组并处理结果。
另一种可能的方法(不是我推荐的方法)是将saparator从逗号更改为其他内容。例如:
SELECT ID, GROUP_CONCAT(NAME SEPARATOR ';') AS CODE
FROM test
GROUP BY ID
(SELECT ID, SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 1), ',', -1) AS CODE1,
If( length(GROUP_CONCAT(NAME)) - length(replace(GROUP_CONCAT(NAME), ',', ''))>1,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 2), ',', -1) ,NULL)as CODE2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 3), ',', -1) AS CODE3,
If( length(GROUP_CONCAT(NAME)) - length(replace(GROUP_CONCAT(NAME), ',', ''))>2,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(NAME), ',', 4), ',', -1) ,NULL)as CODE4
FROM test
GROUP BY ID)