mysql - Mysql程序中如何同时使用两个语法

我想写两个While语法。

我期望的结果如下。

当“ H”
  tagName1，tagName2，tagname3
当“ D”
  tagName1，tagName2，tagname3
当“ M”
  tagName1，tagName2，tagname3
当Y'
  tagName1，tagName2，tagname3

但这没用...

下面是我编写的代码，只播放了一次。

任何建议，请..

DECLARE tagList varchar(255) DEFAULT 'tagName1,tagName2,tagName3';
DECLARE tagTypeList varchar(150) DEFAULT 'H,D,M,Y';
WHILE tagTypeList != '' DO
 WHILE tagList != '' DO
    SET tagNameArray = SUBSTRING_INDEX(tagList, ',', 1);
    SET tagTypeArray = SUBSTRING_INDEX(tagTypeList, ',', 1);

 IF(tagTypeArray = 'H') THEN
        SET aDate = (SELECT (DATE_ADD(nDateTime, INTERVAL -1 HOUR)));
        SET ago_Y = (SELECT DATE_FORMAT(aDate,'%Y'));
        SET ago_M = (SELECT DATE_FORMAT(aDate,'%m'));
        SET ago_D = (SELECT DATE_FORMAT(aDate,'%d'));
        SET ago_H = (SELECT DATE_FORMAT(aDate,'%H'));
        SET ago_W = (SELECT DATE_FORMAT(aDate,'%w'));

 ELSEIF(tagTypeArray = 'D') THEN
        SET aDate = (SELECT (DATE_ADD(nDateTime, INTERVAL -1 DAY)));
        SET ago_Y = (SELECT DATE_FORMAT(aDate,'%Y'));
        SET ago_M = (SELECT DATE_FORMAT(aDate,'%m'));
        SET ago_D = (SELECT DATE_FORMAT(aDate,'%d'));

        SET ago_H = 0;
        SET ago_W = 0;

 ELSEIF(tagTypeArray = 'M')  THEN
        SET aDate = (SELECT (DATE_ADD(nDateTime, INTERVAL -1 MONTH)));
        SET ago_Y = (SELECT DATE_FORMAT(aDate,'%Y'));
        SET ago_M = (SELECT DATE_FORMAT(aDate,'%m'));

        SET ago_D = 0;
        SET ago_H = 0;
        SET ago_W = 0;

 ELSEIF(tagTypeArray = 'Y')   THEN
        SET aDate = (SELECT (DATE_ADD(nDateTime, INTERVAL -1 YEAR)));
        SET ago_Y = (SELECT DATE_FORMAT(aDate,'%Y'));
        SET ago_M = 0;
        SET ago_D = 0;
        SET ago_H = 0;
        SET ago_W = 0;
 END IF;


   SET selectValue = (SELECT tagvalue FROM datasource WHERE tagname = tagNameArray and tagtype = tagTypeArray and y=ago_Y and m=ago_M and d=ago_D and h=ago_H);

   IF(selectValue IS NULL OR selectValue = '')
        THEN

            SET old_Value_3M = (Select LastValue from BwAnalogTable where TagName = tagNameArray and LogDate >= ago_3M order by LogDate desc, LogTime desc limit 1);
           # Here Insert Querty
   END IF;



    IF LOCATE(',', tagList) > 0 THEN
      SET tagList = SUBSTRING(tagList, LOCATE(',', tagList) + 1);
    ELSE
      SET tagList = '';
    END IF;
 END WHILE;


    IF LOCATE(',', tagTypeList) > 0 THEN
      SET tagTypeList = SUBSTRING(tagTypeList, LOCATE(',', tagTypeList) + 1);
   ELSE
      SET tagTypeList = '';
   END IF;
END WHILE;

最佳答案

我修改了代码，如下所示。

DECLARE done  INT DEFAULT FALSE;
DECLARE tagType2 VARCHAR(255) DEFAULT '';
DECLARE tagName2 VARCHAR(255) DEFAULT '';
DECLARE tagType_Cur CURSOR for select tagType from tbtagtype;
DECLARE tagName_Cur CURSOR for select tagName from tbtaglist;

 OPEN tagType_Cur;
         LOOP1: LOOP
                FETCH tagType_Cur INTO tagType2;
                IF done THEN
                       CLOSE tagType_Cur;
                       LEAVE LOOP1;
                END IF;
           ########## here insert your core query ###############
         OPEN tagName_Cur;
                LOOP2: LOOP
                    FETCH tagName_Cur INTO tagName2;

                    IF done THEN
                         CLOSE tagName_Cur;
                         SET done = FALSE;
                         LEAVE LOOP2;
                    END IF;
                    ########## here insert your core query ###############
                  END LOOP LOOP2;
         END LOOP LOOP1;