需求表如下：
单纯的用mysql函数来表达上述关系：
DELIMITER $$USE `euht`$$DROP FUNCTION IF EXISTS `queryCurrentAlarmChildren`$$CREATE DEFINER=`root`@`%` FUNCTION `queryCurrentAlarmChildren`(nodeId CHAR(255),treeLevel CHAR(255)) RETURNS VARCHAR(4000) CHARSET utf8BEGINDECLARE sNodeId VARCHAR(255);DECLARE sCurrentAlarmId VARCHAR(8000);DECLARE sTreeLevel VARCHAR(255);DECLARE sTempLine VARCHAR(4000);DECLARE sTempEdu VARCHAR(4000);DECLARE sTempEbu VARCHAR(4000);DECLARE sWhileInt INT;DECLARE sWhileTotal INT;DECLARE sWhileGroupConcat VARCHAR(1024);DECLARE sWhileConcat VARCHAR(4000);DECLARE sWhileTempValue INT;SET sNodeId = CAST(nodeId AS CHAR);SET sTreeLevel = CAST(treeLevel AS CHAR);SET sCurrentAlarmId = '$';SET sTempLine = '';SET sTempEdu = '';SET sTempEbu = '';SET sWhileInt = 0;SET sWhileTotal = 0;SET sWhileConcat = '';IF sTreeLevel='3' THEN SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE device_id = sNodeId; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE device_id = sNodeId ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));ELSEIF sTreeLevel='2' THEN SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE device_id = sNodeId; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE device_id = sNodeId ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE edu_id = sNodeId; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE edu_id = sNodeId ORDER BY add_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));ELSEIF sTreeLevel='1' THEN SELECT COUNT(*) INTO sWhileTotal FROM cap_edu WHERE line_id = sNodeId; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_edu WHERE line_id = sNodeId LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sTempEdu = CONCAT(sTempEdu,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0 ORDER BY add_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));ELSE SELECT GROUP_CONCAT(id) INTO sTempLine FROM train_line WHERE parent_id = sNodeId; SELECT COUNT(*) INTO sWhileTotal FROM cap_edu WHERE FIND_IN_SET (line_id,IFNULL(sTempLine,''))>0; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_edu WHERE FIND_IN_SET(line_id,sTempLine)>0 LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sTempEdu = CONCAT(sTempEdu,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEdu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM cap_ebu WHERE FIND_IN_SET(edu_id,sTempEdu)>0 ORDER BY add_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sTempEbu = CONCAT(sTempEbu,',',IFNULL(sWhileConcat,'')); SELECT COUNT(id) INTO sWhileTotal FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0; SET sWhileInt = 0; SET sWhileConcat = ''; WHILE sWhileTotal >= (sWhileInt*20) DO SET sWhileTempValue = sWhileInt*20; SELECT GROUP_CONCAT(id) INTO sWhileGroupConcat FROM current_alarm WHERE FIND_IN_SET(device_id,sTempEbu)>0 ORDER BY ceate_time DESC LIMIT sWhileTempValue,20; SET sWhileConcat = CONCAT(sWhileConcat,',',IFNULL(sWhileGroupConcat,'')); SET sWhileInt = sWhileInt + 1; END WHILE; SET sCurrentAlarmId = CONCAT(sCurrentAlarmId,',',IFNULL(sWhileConcat,''));END IF;RETURN sCurrentAlarmId;END$$DELIMITER ;SELECT * FROM current_alarm WHERE FIND_IN_SET(id,queryCurrentAlarmChildren('22fd52a51efb4401b091f944e2051a89','0'))>0; 注意：
编写函数的时候，需要注意几个地方：
使用“GROUP_CONCAT”函数时，能够拼接的最大长度是1kb(1024字节)。否则，超过部分将会忽略。
考虑到这个原因，写函数时，通过分页的思路来进行分段拼接。
使用“GROUP_CONCAT”函数时，如果拼接的来源为零，则拼接的结果为null；如
SELECT GROUP_CONCAT(id) FROM aform WHERE id = '11' ;（不存在id为11的行），执行的结果为null。
使用“CONCAT”函数时，如果存在为null值，则拼接结果为null。如：
SELECT CONCAT('1',',',NULL)；执行结果为null
SELECT CONCAT(NULL,',','2')；执行结果为null
当“DECLARE ”申明了一个变量，而没给该变量初始化时，其默认为null，这里只验证了VARCHAR类型。
后记：上面这种方法能解决需求中问题，可是查询效率极慢，无法忍受。下面这种方法效率得到极大提高。
因为页面会提供两个参数给后台：一个参数是nodeId(当前点击的id)，另一个参数是level（树等级）。
根据level等级来判断执行那个查询语句。
例如：level为0或-1（代表全网），则执行以下查询语句：
SELECT * FROM (SELECT * FROM euht.HISTORY_ALARM ha WHERE ha.DEVICE_ID = (SELECT ceb.ID FROM euht.CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = (SELECT ced.id FROM euht.CAP_EDU ced WHERE ceb.EDU_ID = ced.ID AND ced.LINE_ID = (SELECT tl.ID FROM euht.TRAIN_LINE tl WHERE tl.PARENT_ID = ？ AND tl.ID = ced.LINE_ID))) UNION ALL (SELECT * FROM euht.HISTORY_ALARM ha WHERE ha.DEVICE_ID = (SELECT ced.id FROM euht.CAP_EDU ced WHERE ha.DEVICE_ID = ced.ID AND ced.LINE_ID = (SELECT tl.ID FROM euht.TRAIN_LINE tl WHERE tl.PARENT_ID = ？ AND tl.ID = ced.LINE_ID)))) t LIMIT 0,15
如果level为1（代表京津高铁这一等级），则执行以下查询语句：
SELECT * FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = (SELECT ceb.ID FROM CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = (SELECT ced.id FROM CAP_EDU ced WHERE ceb.EDU_ID = ced.ID AND ced.LINE_ID = #{nodeId,jdbcType=VARCHAR})) UNION ALL SELECT * FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = (SELECT ced.id FROM CAP_EDU ced WHERE ha.DEVICE_ID = ced.ID AND ced.LINE_ID = #{nodeId,jdbcType=VARCHAR}) 如果level为2（代表EDU这一等级），则执行以下查询语句：
SELECT * FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = (SELECT ceb.ID FROM CAP_EBU ceb WHERE ha.DEVICE_ID = ceb.ID AND ceb.EDU_ID = #{nodeId,jdbcType=VARCHAR}) UNION ALL SELECT * FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = #{nodeId,jdbcType=VARCHAR}
如果level为3（代表EBU这一等级），则执行以下查询语句：
SELECT * FROM HISTORY_ALARM ha WHERE ha.DEVICE_ID = #{nodeId,jdbcType=VARCHAR}