select group_concat(DISTINCT f1) from tt union select group_concat(DISTINCT f2) from tt
Select f1,group_concat(f2) from表 group by f1;
DELIMITER $$DROP PROCEDURE IF EXISTS `dt`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `dt`() BEGIN DECLARE done INT DEFAULT 0; DECLARE a1 VARCHAR(10); DECLARE a2 VARCHAR(100); DECLARE cur1 CURSOR FOR SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; SET @ee=''; FETCH cur1 INTO a1,a2; WHILE done=0 DO SET @ee:=CONCAT(@ee,'max(if(f1=\'',a1,'\',dd,\'\'',')) as ',a1,','); FETCH cur1 INTO a1,a2; END WHILE; SELECT @ee; SET @ee=CONCAT('select f1,',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a '); SELECT @ee; PREPARE stmt1 FROM @ee; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; END$$DELIMITER ;
1楼的结果是group_concat(DISTINCT f1) a,b,c aa,bb,bbb,cc 2楼的结果是f1 group_concat(f2) a aa b bb,bbb c cc都不是想要的结果
SET @ee=CONCAT('select f1,',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a ');-> SET @ee=CONCAT('select ',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a ');
DELIMITER $$DROP PROCEDURE IF EXISTS `dt`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `dt`() BEGIN DECLARE done INT DEFAULT 0; DECLARE a1 VARCHAR(10); DECLARE a2 VARCHAR(100); DECLARE cur1 CURSOR FOR SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; SET @ee=''; FETCH cur1 INTO a1,a2; WHILE done=0 DO SET @ee:=CONCAT(@ee,'max(if(f1=\'',a1,'\',dd,\'\'',')) as ',a1,','); FETCH cur1 INTO a1,a2; END WHILE; SET @ee=CONCAT('select ',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a '); PREPARE stmt1 FROM @ee; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1; END$$DELIMITER ;
union
select group_concat(DISTINCT f2) from tt
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a1 VARCHAR(10);
DECLARE a2 VARCHAR(100);
DECLARE cur1 CURSOR FOR SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
SET @ee='';
FETCH cur1 INTO a1,a2;
WHILE done=0 DO
SET @ee:=CONCAT(@ee,'max(if(f1=\'',a1,'\',dd,\'\'',')) as ',a1,',');
FETCH cur1 INTO a1,a2;
END WHILE;
SELECT @ee;
SET @ee=CONCAT('select f1,',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a ');
SELECT @ee;
PREPARE stmt1 FROM @ee;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
END$$DELIMITER ;
a,b,c
aa,bb,bbb,cc
2楼的结果是f1 group_concat(f2)
a aa
b bb,bbb
c cc都不是想要的结果
SET @ee=CONCAT('select ',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a ');
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a1 VARCHAR(10);
DECLARE a2 VARCHAR(100);
DECLARE cur1 CURSOR FOR SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
SET @ee='';
FETCH cur1 INTO a1,a2;
WHILE done=0 DO
SET @ee:=CONCAT(@ee,'max(if(f1=\'',a1,'\',dd,\'\'',')) as ',a1,',');
FETCH cur1 INTO a1,a2;
END WHILE;
SET @ee=CONCAT('select ',LEFT(@ee,LENGTH(@ee)-1),' from (SELECT f1,GROUP_CONCAT(f2) AS dd FROM ttp GROUP BY f1) a ');
PREPARE stmt1 FROM @ee;
EXECUTE stmt1 ;
DEALLOCATE PREPARE stmt1;
END$$DELIMITER ;
MySQL交叉表