Mysql组合查询例子

组合查询例子

查询结果:

代码

drop database if exists cmms;
create database cmms;
use cmms;
drop table if exists bj;
create table bj(
id int not null auto_increment primay key,
sbid varchar(20) default null,
bjmc varchar(20) default null
)engine=InnoDB default charset=utf8 comment '备件表';
INSERT INTO `bj` VALUES (1,'1,3','轴承');
INSERT INTO `bj` VALUES (2,'2,4','弯管');
INSERT INTO `bj` VALUES (3,'1','球体');
INSERT INTO `bj` VALUES (4,'2','轴承');
INSERT INTO `bj` VALUES (5,'3,5','aaa');
INSERT INTO `bj` VALUES (6,'1','电机');
drop table if exists kc;
create table kc(
id int not null auto_increment primary key,
bjid int(11),
bjsl decimal(20,2) default 0,
ckbh varchar(20)
)engine=InnoDB default charset=utf8 comment '库存表';
INSERT INTO `kc` VALUES (1,1,2,'一库');
INSERT INTO `kc` VALUES (2,1,2,'二库');
INSERT INTO `kc` VALUES (3,2,3,'二库');
INSERT INTO `kc` VALUES (4,3,9,'三库');
INSERT INTO `kc` VALUES (5,4,1,'一库');
INSERT INTO `kc` VALUES (6,5,109,'一库');
INSERT INTO `kc` VALUES (7,6,76,'二库');
drop table if exists sb;
create table sb(
id int not null auto_increment primay key,
name varchar(20) default null
)engine=InnoDB default charset=utf8 comment '设备表';
INSERT INTO `sb` VALUES (1,'高炉');
INSERT INTO `sb` VALUES (2,'机修');
INSERT INTO `sb` VALUES (3,'炼铁');
INSERT INTO `sb` VALUES (4,'发电');

DROP FUNCTION IF EXISTS `func_split`;
CREATE DEFINER=`root`@`localhost` FUNCTION `func_split`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(2000) CHARSET utf8
BEGIN
    # 拆分传入的字符串，返回拆分后的新字符串
        declare result varchar(255) default '';
        set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
        return result;
END;

DROP FUNCTION IF EXISTS `func_split_detailtable`;
CREATE DEFINER=`root`@`localhost` FUNCTION `func_split_detailtable`(s_id int ) RETURNS varchar(2000) CHARSET utf8
BEGIN
    # 得到传入备件编号的设备编号字段值     
 declare sbid varchar(2000) DEFAULT '';
 DECLARE stop  int DEFAULT 0;
declare cur_split CURSOR
FOR
SELECT s.sbid from bj s where s.id=s_id ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET  stop=1;
open cur_split;
fetch cur_split into sbid;
close cur_split;
return sbid;
end;

CREATE DEFINER=`root`@`localhost` FUNCTION `func_split_TotalLength`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
BEGIN
    # 计算传入字符串的总length
    return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END;

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_split_mtable`()
BEGIN
declare sid int default 0;
DECLARE stop  int DEFAULT 0;
declare cur_split CURSOR
FOR
SELECT b.id from bj b order by b.id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET  stop=1;
DROP TEMPORARY TABLE IF EXISTS tmp_sp;
create temporary table tmp_sp(
bid int,
bjmc varchar(120),
sbid varchar(128)
)DEFAULT CHARSET=utf8;
open cur_split;
fetch cur_split into sid;
while stop<>1 do
call pro_split_table(sid);
fetch cur_split INTO sid;
end while;
close cur_split;
end;

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_split_table`(s_id int)
BEGIN
declare sid int default 0;
declare str varchar(2000) default '';
declare sts varchar(2000) default '';
DECLARE stop  int DEFAULT 0;
declare cur_split CURSOR
FOR
SELECT s.id,s.bjmc,t.sbid from bj s,tmp_split t where s.id=s_id ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET  stop=1;
call splitString(func_split_detailtable(s_id),',');
open cur_split;
fetch cur_split into sid, str,sts;
while stop<>1 do
INSERT into tmp_sp values(sid,str,sts);
fetch cur_split INTO sid,str,sts;
end while;
close cur_split;
end;

CREATE DEFINER=`root`@`localhost` PROCEDURE `splitString`(IN f_string varchar(1000),IN f_delimiter varchar(5))
BEGIN
# 拆分结果
declare cnt int default 0;
declare i int default 0;
set cnt = func_split_TotalLength(f_string,f_delimiter);
DROP TABLE IF EXISTS `tmp_split`;
create temporary table `tmp_split` (`sbid` varchar(128) not null) DEFAULT CHARSET=utf8;
while i < cnt
do
    set i = i + 1;
    insert into tmp_split(`sbid`) values (func_split(f_string,f_delimiter,i));
end while;
END;


#先把备件表化成明细表，再根据设备编号查询并组合出该设备下有哪些备件编号、名称及数量
call pro_split_mtable();
SELECT * from tmp_sp;
select k.bid,k.bjmc,k.sbname,m.bjsl from(
(select t.bid bid,s.id sbid,t.bjmc bjmc,s.name sbname from tmp_sp t,sb s where s.id=t.sbid)k,
(select b.id bid,sum(k.bjsl) bjsl,b.bjmc bjmc  from bj b, kc k where k.bjid=b.id GROUP by k.bjid)m
) where k.bid=m.bid and k.sbid=1;