求一个存储过程

系统表 mysystem
systemid
systemname(系统分三类) (每类系统下还会有多个项目,各类系统下项目的基本信息字段是一样的，但各类系统的参数不一样(个数，值)，每类系统下的不同项目的参数是一样的，但值有可能不一样（这里又分为多组）)
总的来说就是：三类系统，每个系统还都会分为多组，下在再有多个项目。
create table mysystem (
systemid int(10) NOT NULL AUTO_INCREMENT,
systemname varchar(20),
primary key(systemid)
);
insert into mysystem (systemname) values('systemA');
insert into mysystem (systemname) values('systemB');
insert into mysystem (systemname) values('systemC');
项目表 project (每套系统下又分多个组)projectid
projectname
systemnum
groupnum
create table project(
projectid int(10) NOT NULL AUTO_INCREMENT,
projectname varchar(20),
systemnum int(10),
groupnum int(10),
primary key(projectid)
);
insert into project (projectname,systemnum,groupnum) values('projectA01',1,1);
insert into project (projectname,systemnum,groupnum) values('projectA02',1,1);
insert into project (projectname,systemnum,groupnum) values('projectA03',1,2);
insert into project (projectname,systemnum,groupnum) values('projectA04',1,2);
insert into project (projectname,systemnum,groupnum) values('projectA05',1,3);
insert into project (projectname,systemnum,groupnum) values('projectA06',1,3);insert into project (projectname,systemnum,groupnum) values('projectB01',2,1);
insert into project (projectname,systemnum,groupnum) values('projectB02',2,1);
insert into project (projectname,systemnum,groupnum) values('projectB03',2,2);
insert into project (projectname,systemnum,groupnum) values('projectB04',2,2);
insert into project (projectname,systemnum,groupnum) values('projectB05',2,3);
insert into project (projectname,systemnum,groupnum) values('projectB06',2,3);insert into project (projectname,systemnum,groupnum) values('projectC01',3,1);
insert into project (projectname,systemnum,groupnum) values('projectC02',3,1);
insert into project (projectname,systemnum,groupnum) values('projectC03',3,2);
insert into project (projectname,systemnum,groupnum) values('projectC04',3,2);
insert into project (projectname,systemnum,groupnum) values('projectC05',3,3);
insert into project (projectname,systemnum,groupnum) values('projectC06',3,3);
参数表 parametertable
pname
pvalue
systemnum
groupnum每个参数都指定了名子，值，所属系统，所属组(如果systemnum为0表示所有系统都有这个参数，如果groupnum为0则表示所有组都有这个参数)create table parametertable (
pname varchar(20) NOT NULL ,
pvalue double,
systemnum int(10),
groupnum int(10),
primary key(pname));
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs01',1.1,1,1);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs02',1.2,1,2);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs03',2.1,2,1);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs04',2.2,2,2);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs05',3.1,3,1);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs06',3.2,3,2);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs07',0.1,0,0);
insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs08',0.2,0,0);需要一个存储过程 proc(in systemnum int)call proc(1)     返回所有第一套系统的所有项目的集合。这里面包括的对象是有要求的，如下：public class ProjectA {
private int projectid;
private String projectname;
private int systemnum;
private int groupnum;
private double cs;
//此属性比特殊,如果此项目是A系统的第一类(即：groupnum=1)刚对应参数cs01
//如果此项目是A系统的第二类(即：groupnum=2)刚对应参数cs02
private double cs07; //公共的参数
private double cs08; //公共的参数

}

解决方案 »

  1.   

    ProjectA  对象里面的cs(参数)属性,是跟据这个项目所属的组号来的。如果是一组的。它会返回属于第N套系统的，第一组的参数。（比如：cs01），如果是二组的刚对应的值是cs02的值
    public class ProjectA {
    private int projectid;
    private String projectname;
    private int systemnum;
    private int groupnum;
    private double cs;
    //此属性比特殊,如果此项目是A系统的第一类(即：groupnum=1)刚对应参数cs01
    //如果此项目是A系统的第二类(即：groupnum=2)刚对应参数cs02
    private double cs07; //公共的参数
    private double cs08; //公共的参数

    }描述的可能有些不清楚。请留言，我再解释
      

  2.   


    比如存储过程的名字叫：proc()
    call proc(1);返回的是系统一的所有的项目的一个结果集。（之前自己写了一个用游标。返回多个结果集，但我只能拿到一个）它的那个cs属性是按它的组号分别去不同的参数值的（cs01,cs02等）
      

  3.   

    比如　
    call proc(1);
    要求结果是什么贴出来
      

  4.   

    projectid projectname systemnum groupnum cs
    1 projectA01 1 1 1.1
    2 projectA02 1 1 1.2
    3 projectA03 1 2 2.1
    4 projectA04 1 2 2.2
    5 projectA05 1 3 3.1
    6 projectA06 1 3 3.2
      

  5.   

    发现在个小错误，
    上面参数表中插入的数据少了三条。每个系统都分了3类，可参数只给了2类的。现在补一下。insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs09',1.3,1,3);
    insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs10',2.3,2,3);
    insert into parametertable (pname,pvalue,systemnum,groupnum) values('cs10',3.3,3,3);
      

  6.   

    认真看了下楼主的描述,觉得应该不是很难,只是觉得楼主给出的数据和想要的结果貌似不一致.不知道是不是我没理解到.比如楼主结果表中systemnum groupnum 都是1的情况第二条对于cs值为1.2.但是表三中cs 1.2的groupnum又是2.....   不解之下照结果写了一句sql 竟然可以的出结果,select b.projectid, b.projectname,b.systemnum, b.groupnum,c.pvalue from mysystem a, project b, parametertable c where right(b.projectname,2)=right(c.pname,2) and a.systemid=1 and b.systemnum=1;
      

  7.   

    projectid=1、2同属于1组，为什么CS不同？
      

  8.   

    SELECT A.*,C.`pvalue`,D.CS07,E.CS08 FROM mysystem a1 INNER JOIN project a
    ON a1.`systemid`=a.`systemnum`
    INNER JOIN parametertable c ON RIGHT(a.projectname,2)=RIGHT(c.pname,2)
    INNER JOIN
    (SELECT pname,`pvalue` AS CS07 FROM parametertable WHERE pname='cs07') D
    ON 1=1
    INNER JOIN
    (SELECT pname,`pvalue` AS CS08 FROM parametertable WHERE pname='cs08') E
    ON 1=1
    WHERE a.`systemnum`=1;
      

  9.   

    实际需求是这样，再向参数表里面添加几个参数。insert into parametertable (pname,pvalue,systemnum,groupnum) values('csA',9.1,1,0);
    insert into parametertable (pname,pvalue,systemnum,groupnum) values('csB',9.2,2,0);
    insert into parametertable (pname,pvalue,systemnum,groupnum) values('csC',9.3,3,0);
    insert into parametertable (pname,pvalue,systemnum,groupnum) values('csC1',9.31,3,0);
    这三个参数是特殊的。它们的名子是csA,csB,csC,表示对应三个类系统的。然后关键是groupnum都是0
    表示此参数对这类下的所有的组都是有用。
    各类系统的参数个数也有可能不同。这样有可能需要三句像楼上给的SQL语句，只是字段不同。
    所以数据结果最终是下面这样子的：
      

  10.   

    DELIMITER $$
    DROP PROCEDURE IF EXISTS `te`$$CREATE PROCEDURE `te`(aid INT)
    BEGIN
    SET @a='';
    SET @c=' INNER JOIN (SELECT pname,`pvalue` AS ' ;
    SET @d=ASCII('f');
    SET @asql1='SELECT A.*,C.`pvalue`,D.CS07,E.CS08,';
    SELECT 
    @asql1:=CONCAT(@asql1,CHAR(@d),'.',pname,','),
    @a:=
    CONCAT(@a,@c,pname,' FROM parametertable WHERE pname=\'',pname,'\') ',CHAR(@d), ' on 1=1') ,
    @d:=@d+1
    FROM parametertable b WHERE b.`groupnum`=0 AND b.`systemnum`=aid;
    SET @asql=' FROM mysystem a1 INNER JOIN project a
     ON a1.`systemid`=a.`systemnum`
     INNER JOIN parametertable c ON RIGHT(a.projectname,2)=RIGHT(c.pname,2)
     INNER JOIN
     (SELECT pname,`pvalue` AS CS07 FROM parametertable WHERE pname=\'cs07\') D
     ON 1=1
     INNER JOIN
     (SELECT pname,`pvalue` AS CS08 FROM parametertable WHERE pname=\'cs08\') E
     ON 1=1 ';
     SET @asql=CONCAT(LEFT(@asql1,LENGTH(@asql1)-1),@asql,@a,' WHERE a.`systemnum`=1');
     PREPARE stml FROM @asql;
     EXECUTE stml;
     
    END$$DELIMITER ;
      

  11.   

    我试了一下，没有达到想要的效果，感觉原因是在这句：
    INNER JOIN parametertable c ON RIGHT(a.projectname,2)=RIGHT(c.pname,2)给的数据是简化了的，每类系统只有6个项目，结尾都是01\02\03\04\05\06这样统一的。
    但实际中系统个数会更多，会出现在ProjecaA11,ProjecaA31,ProjecaA43
    贴一下实际project表吧。
    还有实际的parametertable表()各位费心了
      

  12.   

    我整理了一下数据，传到网上了。麻烦下一下吧。这样比描术的更省事儿。我再简单描述一下吧：
    然后我在java中做了三个类，分别对应该A，B，C类系统public class ProjectA {
    private int projectid;
    private String projectname;
    private int systemnum;
    private int groupnum;
    private Double xfjz_f;   
    //(可能是参数表中xfjz_f02,xfjz_f02,xfjz_f03,xfjz_f04,xfjz_f05,xfjz_f06,xfjz_f07中一个的值)
    //跟据它所属组决定的
    private Double ere;
    private Double tariff;
    private Double gre;
    private Double gasprices;
    }
    public class ProjectB {
    private int projectid;
    private String projectname;
    private int systemnum;
    private int groupnum;   
    //(可能是参数表中shrs_f01,shrs_f02中一个的值)
    //跟据它所属组决定的
    private Double shrs_f;
    }
    public class ProjectC {
    private int projectid;
    private String projectname;
    private int systemnum;
    private int groupnum; private Double xfkz_f1;
    private Double xfkz_f2;
    private Double xfkz_f3;
    private Double xfkz_f4;
    private Double xfkz_f5;
    private Double erg;
    private Double grco2;
    private Double grso2;
    private Double grno;
    private Double grdust;

    }
    想要返回的结果集：
    不管是用sql语句也好，用存储过程也好，参数是系统号，返回这类系统下所有的项目，如果系统下有分组，刚参数按分好的组去取对应该的参数。图：数据上传到网上了，地址：http://download.csdn.net/detail/ghosthuo/4906353
    新建一库，直接导入就可以了。
      

  13.   

    对于表三中ere和gre这种systemnum和groupnum分别相同的情况表示不会求解.坐等大神吧.
    还有就是楼主数据和描述依然有点小出入,不过不影响大局.
      

  14.   

    用你上传的数据，要求结果是什么，比如systemnum=1
      

  15.   


    数据是跟把systemnum这个值来的。现在库里面系统类的id 已经从4开始了。
    A类ID：  4
    B类ID:   5
      

  16.   

    `systemnum`=5时，为什么没有
    tariff、gasprices这2项，4时有？
      

  17.   

    `systemnum`=5时，为什么没有
     tariff、gasprices这2项?
      

  18.   

    参数的systemnum=0时表示各系统公共的属性，groupnum=0时表示各组公共的属性。
    我做数据的时候没有注意到。不好意思了。
      

  19.   

    @qq为systemnu，自行修改
    SET @qq=4;SELECT MAX(LEFT(b.`pname`,6)) INTO @gg FROM `project` a,`parametertable` b 
    WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
    AND a.`systemnum`=@qq;
    SELECT @gg;SET @asql=CONCAT('SELECT a.*,b.`pvalue` as ',@gg,', d.* FROM `project` a,`parametertable` b ,');
    SET @a='';
    SELECT @a:=CONCAT(@a,pvalue,' as ',pname,', ') FROM `parametertable` WHERE (`systemnum`=@qq AND `groupnum`=0) OR (`systemnum`=0 AND `groupnum`=0);
    SET @a=CONCAT('(select ',LEFT(@a,CHAR_LENGTH(@a)-2),') d');
    SELECT @a;
    SET @asql=CONCAT(@asql,@a,' WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
    AND a.`systemnum`=',@qq);
    SELECT @asql;
     PREPARE stml FROM @asql;
     EXECUTE stml;
      

  20.   

    or
    DELIMITER $$
    DROP PROCEDURE IF EXISTS te$$
    CREATE PROCEDURE te(aid INT)
    BEGIN
    SELECT aid;
     SELECT MAX(LEFT(b.`pname`,6)) INTO @gg FROM `project` a,`parametertable` b 
     WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
     AND a.`systemnum`=aid; SET @asql=CONCAT('SELECT a.*,b.`pvalue` as ',@gg,', d.* FROM `project` a,`parametertable` b ,');
     SET @a='';
     SELECT @a:=CONCAT(@a,pvalue,' as ',pname,', ') FROM `parametertable` WHERE (`systemnum`=aid AND `groupnum`=0) OR (`systemnum`=0 AND `groupnum`=0);
     SET @a=CONCAT('(select ',LEFT(@a,CHAR_LENGTH(@a)-2),') d'); SET @asql=CONCAT(@asql,@a,' WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
     AND a.`systemnum`=',aid);  PREPARE stml FROM @asql;
      EXECUTE stml;
      END$$
    DELIMITER ;call te(4)
      

  21.   

    DELIMITER $$USE `hytest`$$DROP PROCEDURE IF EXISTS `te`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `te`(aid INT)
    BEGIN
    DROP TABLE IF EXISTS TT;
    SELECT aid;
     SELECT MAX(LEFT(b.`pname`,6)) INTO @gg FROM `project` a,`parametertable` b 
     WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
     AND a.`systemnum`=aid;
     SET @asql=CONCAT('SELECT a.*,b.`pvalue` as ',@gg,', d.* FROM `project` a,`parametertable` b ,');
     SET @a='';
     SELECT @a:=CONCAT(@a,pvalue,' as ',pname,', ') FROM `parametertable` WHERE (`systemnum`=aid AND `groupnum`=0) OR (`systemnum`=0 AND `groupnum`=0);
     SET @a=CONCAT('(select ',LEFT(@a,CHAR_LENGTH(@a)-2),') d');
     SET @asql=CONCAT('CREATE TABLE TT AS ',@asql,@a,' WHERE a.`groupnum`=b.`groupnum` AND a.`systemnum`=b.`systemnum`
     AND a.`systemnum`=',aid);
      PREPARE stml FROM @asql;
      EXECUTE stml;
      END$$DELIMITER ;访问TT表即可