mysql 存储过程如何合并结果集

jiajing1990_ 2015-06-09 10:33:07

得到的结果集是这样的可能有一个也可能有很多个是循环根据条件查出来的如何合并呀求大神解惑

...全文

1022 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

fcqm8888 2016-01-29

打赏
举报

有了结果，已经努力了，继续努力。

懒懒的吉他手 2015-06-10

打赏
举报

太长，不看，死小贾

ACMAIN_CHM 2015-06-10

打赏
举报

建议楼主提供测试用例。建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

道玄希言 2015-06-09

打赏
举报

我是觉得你SP可以参考这个帖的写法了. http://www.2cto.com/database/201209/152513.html 你这么写, 又不想用临时表, 我还真不知道怎么合并.

wwwwb 2015-06-09

打赏
举报

插入临时表中再GROUP＿CONAT合并，否则修改你的SP

道玄希言 2015-06-09

打赏
举报

用递归查询可以不?

jiajing1990_ 2015-06-09

打赏
举报

引用 8 楼 wwwwb 的回复:

你要怎样合并，上述代码运行结果是什么？插入临时表中再合并　

结果运行的就是以下

这样的每个结果的字段都是一样的就是内容有变化如果不用临时表有什么办法合并呢

wwwwb 2015-06-09

打赏
举报

你要怎样合并，上述代码运行结果是什么？插入临时表中再合并　

jiajing1990_ 2015-06-09

打赏
举报

引用 6 楼 wwwwb 的回复:

举例说明，具体一点

例如 DROP PROCEDURE IF EXISTS p_test ; CREATE PROCEDURE p_test( scompanycode VARCHAR(8), iuserid VARCHAR(11), idepartmentid VARCHAR(50), nowdate VARCHAR(50) ) BEGIN DECLARE userid INT;-- 用户ID DECLARE deptid INT;-- 部门ID DECLARE countFlag INT; SET @countFlag = 0; WHILE idepartmentid IS NOT NULL DO IF iuserid IS NOT NULL THEN -- 判断用户iD是否为空 SET userid = CAST(iuserid AS DECIMAL(8,0)); -- 赋值 SELECT @countFlag:=@countFlag + 1 AS po ,tt.iuserid,tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,t.sweekday sweekday, t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,tw.iwarnlevel iwarnlevel,tw.swarnname swarnname FROM tcheckportpolicy tt LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid WHERE tt.istate = 0 AND tt.scompanycode= scompanycode AND tt.iuserid= userid AND date_format(tt.dstartdate,'%Y-%m-%d') <= date_format(nowdate,'%Y-%m-%d') AND date_format(tt.denddate,'%Y-%m-%d') >=date_format(nowdate,'%Y-%m-%d') AND tt.isornotcheckport = 1 ORDER BY tt.ipriority DESC; SET iuserid = NULL; ELSE SET deptid = CAST(idepartmentid AS DECIMAL(8,0)); SELECT @countFlag:=@countFlag + 1 AS po ,tt.iuserid, tt.idepartmentid,tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,t.sweekday sweekday, t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,tw.iwarnlevel iwarnlevel,tw.swarnname swarnname FROM tcheckportpolicy tt LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid WHERE tt.istate = 0 AND tt.scompanycode= scompanycode AND tt.idepartmentid= deptid AND date_format(tt.dstartdate,'%Y-%m-%d') <= date_format(nowdate,'%Y-%m-%d') AND date_format(tt.denddate,'%Y-%m-%d') >=date_format(nowdate,'%Y-%m-%d') AND tt.isornotcheckport = 1 ORDER BY tt.ipriority DESC; IF deptid = -1 THEN SET idepartmentid = NULL; ELSEIF deptid = -3 OR deptid = -2 THEN SET idepartmentid = '-1'; ELSE IF deptid > 0 THEN -- 员工 SELECT iparentdepartmentid INTO idepartmentid FROM tdepartment d WHERE d.idepartmentid = idepartmentid; IF idepartmentid = '-1' THEN SET idepartmentid = '-3'; END IF; ELSE -- 来宾 SELECT iguestclassifyparentid INTO idepartmentid FROM tguestclassify g WHERE g.iguestclassifyid = ABS(idepartmentid); IF idepartmentid <> '-2' THEN SET idepartmentid = CONCAT('-',idepartmentid); END IF; END IF; END IF; END IF; END WHILE; END 我这段代码是根据条件判断一直遍历部门的上级部门然后查出来对应的信息这样子每个部门都有结果集但是我想合并到一起

wwwwb 2015-06-09

打赏
举报

举例说明，具体一点

jiajing1990_ 2015-06-09

打赏
举报

引用 2 楼 wwwwb 的回复:

要求结果贴出来

而且我还要对这个合并的结果集进行分组然后去其中符合条件的数据

jiajing1990_ 2015-06-09

打赏
举报

引用 2 楼 wwwwb 的回复:

要求结果贴出来

要求就是能把结果1 到结果N合并到一起展示不使用临时表感觉太好性能了

jiajing1990_ 2015-06-09

打赏
举报

引用 1 楼 yangb0803 的回复:

如果字段相同, 可以直接用 UNION ALL 合并 select a, b,c from tableA where 条件A union all select a,b,c from tableA where 条件B union all ......

我的代码是这样的 DROP PROCEDURE IF EXISTS p_test ; CREATE PROCEDURE p_test( scompanycode VARCHAR(8), iuserid VARCHAR(11), idepartmentid VARCHAR(50), nowdate VARCHAR(50) ) BEGIN DECLARE userid INT;-- 用户ID DECLARE deptid INT;-- 部门ID DECLARE countFlag INT; SET @countFlag = 0; WHILE idepartmentid IS NOT NULL DO IF iuserid IS NOT NULL THEN -- 判断用户iD是否为空 SET userid = CAST(iuserid AS DECIMAL(8,0)); -- 赋值 SELECT @countFlag:=@countFlag + 1 AS po ,tt.iuserid,tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,t.sweekday sweekday, t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,tw.iwarnlevel iwarnlevel,tw.swarnname swarnname FROM tcheckportpolicy tt LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid WHERE tt.istate = 0 AND tt.scompanycode= scompanycode AND tt.iuserid= userid AND date_format(tt.dstartdate,'%Y-%m-%d') <= date_format(nowdate,'%Y-%m-%d') AND date_format(tt.denddate,'%Y-%m-%d') >=date_format(nowdate,'%Y-%m-%d') AND tt.isornotcheckport = 1 ORDER BY tt.ipriority DESC; SET iuserid = NULL; ELSE SET deptid = CAST(idepartmentid AS DECIMAL(8,0)); SELECT @countFlag:=@countFlag + 1 AS po ,tt.iuserid, tt.idepartmentid,tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,t.sweekday sweekday, t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,tw.iwarnlevel iwarnlevel,tw.swarnname swarnname FROM tcheckportpolicy tt LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid WHERE tt.istate = 0 AND tt.scompanycode= scompanycode AND tt.idepartmentid= deptid AND date_format(tt.dstartdate,'%Y-%m-%d') <= date_format(nowdate,'%Y-%m-%d') AND date_format(tt.denddate,'%Y-%m-%d') >=date_format(nowdate,'%Y-%m-%d') AND tt.isornotcheckport = 1 ORDER BY tt.ipriority DESC; IF deptid = -1 THEN SET idepartmentid = NULL; ELSEIF deptid = -3 OR deptid = -2 THEN SET idepartmentid = '-1'; ELSE IF deptid > 0 THEN -- 员工 SELECT iparentdepartmentid INTO idepartmentid FROM tdepartment d WHERE d.idepartmentid = idepartmentid; IF idepartmentid = '-1' THEN SET idepartmentid = '-3'; END IF; ELSE -- 来宾 SELECT iguestclassifyparentid INTO idepartmentid FROM tguestclassify g WHERE g.iguestclassifyid = ABS(idepartmentid); IF idepartmentid <> '-2' THEN SET idepartmentid = CONCAT('-',idepartmentid); END IF; END IF; END IF; END IF; END WHILE; END 没法用union all

wwwwb 2015-06-09

打赏
举报

要求结果贴出来

道玄希言 2015-06-09

打赏
举报

如果字段相同, 可以直接用 UNION ALL 合并 select a, b,c from tableA where 条件A union all select a,b,c from tableA where 条件B union all ......

道玄希言 2015-06-09

打赏
举报

看下这样写怎样, 有错误, 需要修改, 我沒模拟数据. 提供个大概思路: 將你获取 deptid 部分上移, 先组合出合乎条件的 deptid 列表. 然后根据用户值是否为空, 来选择是否需要查询用户相等的资料.


DROP PROCEDURE IF EXISTS p_test ;
CREATE PROCEDURE p_test(
scompanycode VARCHAR(8),
iuserid VARCHAR(11),
idepartmentid VARCHAR(50),
nowdate VARCHAR(50)
)
BEGIN
  DECLARE userid INT;-- 用户ID
  DECLARE deptid INT;-- 部门ID
  DECLARE countFlag INT;
  DECLARE lst VARCHAR(1000);

  SET lst = '$';
  SET @countFlag = 0;
  SET userid = CAST(iuserid AS DECIMAL(8,0)); -- 赋值
  WHILE idepartmentid IS NOT NULL DO
    SET deptid =CAST(idepartmentid AS DECIMAL(8,0)); 
    SET lst = CONCAT(lst, ',', deptid);
    IF deptid = -1 THEN
      SET idepartmentid = NULL;    
    ELSEIF deptid = -3 OR deptid = -2 THEN
      SET idepartmentid = '-1';
    ELSE
      IF deptid > 0 THEN -- 员工
        SELECT iparentdepartmentid INTO idepartmentid 
        FROM tdepartment d WHERE d.idepartmentid = idepartmentid;
      IF idepartmentid = '-1' THEN
        SET idepartmentid = '-3';
      END IF;
    ELSE -- 来宾
      SELECT iguestclassifyparentid INTO idepartmentid 
      FROM tguestclassify g WHERE g.iguestclassifyid = ABS(idepartmentid);
      IF idepartmentid <> '-2' THEN
        SET idepartmentid = CONCAT('-',idepartmentid);
      END IF;
    END IF;
  END WHILE; 
  
  IF iuserid IS NOT NULL THEN
    SELECT DISTINCT @countFlag:=@countFlag + 1 AS po ,tt.iuserid, tt.idepartmentid,
      tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,
      t.sweekday sweekday,t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,
      tw.iwarnlevel iwarnlevel,tw.swarnname swarnname
    FROM tcheckportpolicy tt 
    LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime
    LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime
    LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid
    WHERE tt.istate = 0 
    AND tt.isornotcheckport = 1 
    AND DATE_FORMAT(tt.dstartdate,'%Y-%m-%d') <= DATE_FORMAT(nowdate,'%Y-%m-%d')
    AND DATE_FORMAT(tt.denddate,'%Y-%m-%d') >=DATE_FORMAT(nowdate,'%Y-%m-%d')
    AND tt.scompanycode= scompanycode 
    AND tt.iuserid= userid OR FIND_IN_SET(tt.idepartmentid, lst)
    ORDER BY tt.ipriority DESC;  
    SET iuserid = NULL;	    
  ELSE
    SELECT DISTINCT @countFlag:=@countFlag + 1 AS po ,tt.iuserid, tt.idepartmentid,
      tt.spolicyaction,tt.ipriority,tt.dstartdate,tt.denddate,tt.ilogrecord,
      t.sweekday sweekday,t.stimeperiod stimeperiod,tp.spromptinfo spromptinfo,
      tw.iwarnlevel iwarnlevel,tw.swarnname swarnname
    FROM tcheckportpolicy tt 
    LEFT JOIN tperiod t ON t.iperiodid=tt.sstarttime
    LEFT JOIN tpromptinfo tp ON tp.ipromptinfoid=tt.sendtime
    LEFT JOIN twarnlevel tw ON tw.iwarnlevelid = tt.iwarnlevelid
    WHERE tt.istate = 0 
    AND tt.isornotcheckport = 1
    AND DATE_FORMAT(tt.dstartdate,'%Y-%m-%d') <= DATE_FORMAT(nowdate,'%Y-%m-%d')
    AND DATE_FORMAT(tt.denddate,'%Y-%m-%d') >=DATE_FORMAT(nowdate,'%Y-%m-%d') 
    AND tt.scompanycode= scompanycode 
    AND FIND_IN_SET(tt.idepartmentid, lst)
    ORDER BY tt.ipriority DESC;
  END IF;
END

jiajing1990_ 2015-06-09