CREATE TABLE `tbC` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL,
  `sType` varchar(50) NOT NULL,
  `createtime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbB` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned default NULL,
  `username` varchar(50) default NULL,
  `password` varchar(50) default NULL,
  `sGroup` varchar(50) default NULL,
  `sCet` varchar(50) default NULL,
  `sType` varchar(50) default '',
  `realname` varchar(50) default NULL,
  `detail` varchar(500) default NULL,
  `dtlasttime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbA` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned default NULL,
  `username` varchar(50) default NULL,
  `dtltime` datetime default NULL,
  `sType` varchar(50) NOT NULL,
  `Prices` decimal(10,0) NOT NULL,
  `sTime` varchar(50) NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tbD` (
  `id` int(11) NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL,
  `datetime1` datetime NOT NULL,
  `datetime2` datetime NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE PROCEDURE `get_rpb` (pid int,sid int)
BEGIN

    DECLARE dtime1,dtime2 datetime;

    SELECT `datetime1`,`datetime2` into dtime2,dtime1 FROM `tbD` WHERE (`pid` = pid) AND (`id`=sid);

    SELECT sType,sGroup,sCet,SUM(sTime) AS total,SUM(Prices) AS totalprice FROM (
        SELECT 
            a.*,b.sGroup,B.sCet 
        FROM 
            tbA a,tbB b,tbC c
        WHERE
            (a.pid=b.pid) AND (a.pid=c.pid) AND (a.pid=pid) AND (a.username=b.username) AND (a.sType=c.sType)
    ) AS ABC WHERE (`createtime` between dtime1 and dtime2) GROUP BY sType,sGroup,sCet;
END;