สอบถามการ Query ข้อมูลด้วย MySQL ครับ โดยดึงข้อมูลจากฟิลด์ที่เก็บข้อมูลคั่นด้วยคอมม่า

select a, REPLACE(a, ',', '') b
, LENGTH(a)  x
, LENGTH(REPLACE(a, ',', '')) Y
, LENGTH(a)  - LENGTH(REPLACE(a, ',', '')) + 1 z
from (select 'a,b,cc,dddd' a) t

DROP TABLE IF EXISTS `tbl_test`;
CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `syndrome` varchar(50) DEFAULT NULL,
  `school_name` varchar(50) DEFAULT NULL,
  `class` varchar(20) DEFAULT NULL,
  `room` varchar(10) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`),
  KEY `school_name` (`school_name`),
  KEY `class` (`class`),
  KEY `room` (`room`),
  KEY `syndrome` (`syndrome`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

INSERT INTO `tbl_test` (`id`,`syndrome`,`school_name`,`class`,`room`,`created_at`,`updated_at`) VALUES 
 (1,'1,16','school7','อนุบาล2','4',NULL,NULL),
 (2,'1,13,11,17','school7','อนุบาล1','1',NULL,NULL),
 (3,'1,2,3,4,5','school7','อนุบาล1','1',NULL,NULL),
 (4,'1,16','school7','อนุบาล2','4',NULL,NULL),
 (5,'1,16','school7','อนุบาล2','4',NULL,NULL),
 (6,NULL,'school7','อนุบาล2','1',NULL,NULL),
 (7,'1,11,12','school7','อนุบาล2','1',NULL,NULL),
 (8,'7,8,9','school6','เตรียมอนุบาล','2',NULL,NULL),
 (9,NULL,'school6','เตรียมอนุบาล','2',NULL,NULL),
 (10,'7,8,9','school6','เตรียมอนุบาล','2',NULL,NULL),
 (11,'1,4,5,6','school3','ประถมศึกษาปีที่ 2','2',NULL,NULL),
 (12,NULL,'school3','ประถมศึกษาปีที่ 2','2',NULL,NULL),
 (13,'1,4,5,6','school3','ประถมศึกษาปีที่ 2','2',NULL,NULL);

SELECT
	x.`school_name`,
	x.`class`,
	x.`room`,
	x.`syndrome`,
	(LENGTH(x.`syndrome`) - LENGTH(REPLACE(x.`syndrome`,',','')) + 1) AS `num_rows`
FROM
	(
		SELECT 
			`school_name`,
			`class`,
			`room`,
			GROUP_CONCAT(`syndrome` ORDER BY `id` SEPARATOR ',') AS `syndrome`
		FROM
			`tbl_test`
		GROUP BY
			`school_name`, `class`, `room`
	) x
ORDER BY
	x.`school_name` ASC, x.`class` ASC, x.`room` ASC;

SELECT
	x.`school_name`,
	x.`class`,
	x.`room`,
	x.`syndrome`,
	(LENGTH(x.`syndrome`) - LENGTH(REPLACE(x.`syndrome`,',','')) + 1) AS `num_rows`
FROM
	(
		SELECT 
			`school_name`,
			`class`,
			`room`,
			GROUP_CONCAT(DISTINCT `syndrome` ORDER BY `id` SEPARATOR ',') AS `syndrome`
		FROM
			`tbl_test`
		GROUP BY
			`school_name`, `class`, `room`
	) x
ORDER BY
	x.`school_name` ASC, x.`class` ASC, x.`room` ASC;