MySQL GROUP_CONCAT与COLUMN SPLIT [英] MySQL GROUP_CONCAT with COLUMN SPLIT

问题描述

我正在使用TABLE，需要逻辑帮助.

I am working with a TABLE, need logical help.

检查下面的URL以获取表结构和示例数据.

Check the below URL for the table structure and sample data.

http://sqlfiddle.com/#!2/ece06/2

表架构:

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

如您所见，查询的输出具有逗号连字符.目前，我们正在使用PHP进行字符串concat，在显示时会拆分！

As you can see the output of the query has concat with comma. And currently we are doing string concat using PHP which splits while displaying!!

是否还有其他方法可以拆分RESULT并在列和相同的ROW中显示每个值?同样的结果吗?

Is there any other way to split the RESULT and show each value in column and same ROW? In same result?

注意:每个ROW的代码可能有所不同.

Note: The CODE might vary for each ROW.

推荐答案

在我同事的帮助下，我们到了解决此问题的关键时刻.希望有人可能需要它.如果有人使它变得更简单，这是受欢迎的.

With Help from my colleague We arrived at a point to solve this senario. Hope some one might needed it.. It is welcome if some one make it much simpler.

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

这篇关于MySQL GROUP_CONCAT与COLUMN SPLIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！