有如下数据库DROP DATABASE IF EXISTS `bykvy`;
CREATE DATABASE `bykvy`;
USE `bykvy`;
DROP TABLE IF EXISTS `orginfo`;
CREATE TABLE `orginfo` (
`id` int(10) NOT NULL auto_increment,
`code` varchar(255) NOT NULL,
`orgflag` tinyint(1) default '0' COMMENT '0 表示直属机关 1 表示分支机构',
`orgname` varchar(40) default NULL,
`orgdesc` text,
PRIMARY KEY (`id`),
UNIQUE KEY `UQ_orgInfo_code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `orginfo` VALUES (1,'1001',1,'test','test');
INSERT INTO `orginfo` VALUES (2,'1001:001',1,'test1','test1');
INSERT INTO `orginfo` VALUES (3,'1001:001:001',1,'test11','test11');
INSERT INTO `orginfo` VALUES (4,'1001:001:002',1,'test12','test12');
INSERT INTO `orginfo` VALUES (5,'1001:001:003',1,'test13','test13');
INSERT INTO `orginfo` VALUES (6,'1001:001:004',1,'test14','test14');
INSERT INTO `orginfo` VALUES (7,'1001:002',1,'test2','test2');
INSERT INTO `orginfo` VALUES (8,'1001:002:001',1,'test111','test111');
INSERT INTO `orginfo` VALUES (9,'1001:002:002',1,'test112','test112');
INSERT INTO `orginfo` VALUES (10,'1001:002:003',1,'test113','test113');
INSERT INTO `orginfo` VALUES (11,'1001:002:004',1,'test114','test114');
INSERT INTO `orginfo` VALUES (12,'1001:003',1,'test3','test3');
INSERT INTO `orginfo` VALUES (13,'1001:003:001',1,'test1111','test1111');
INSERT INTO `orginfo` VALUES (14,'1001:003:002',1,'test1112','test1112');
INSERT INTO `orginfo` VALUES (15,'1001:003:003',1,'test1113','test1113');
INSERT INTO `orginfo` VALUES (16,'1001:003:004',1,'test1114','test1114');
INSERT INTO `orginfo` VALUES (17,'1001:004',1,'test4','test4');
INSERT INTO `orginfo` VALUES (18,'1001:005',1,'test5','test5');CREATE FUNCTION `getNums`(checkstr VARCHAR(200),separatorStr VARCHAR(10) ) RETURNS int(11)
begin
DECLARE returnVal int default 0;
DECLARE tmpstr VARCHAR(200) default '';
set checkstr = LCASE(LTRIM(rtrim(checkstr)));
set tmpstr = checkstr;
WHILE LENGTH(tmpstr) > 0 DO
if locate(separatorStr,tmpstr) <= 0 then
return returnVal;
end if;
select returnVal+1 into returnVal;
set tmpstr = SUBSTRING(tmpstr,LOCATE(separatorStr,tmpstr)+length(separatorStr)+1);
END WHILE;
return returnVal;
end;
怎么用一条语句查出数据对应的上一级
例如1001:001 1001:002对应的就是1001
1001:001:003 对应的就是1001:001
等等
也就是把select * from orginfo as o left join (select code as rootcode,orgname as rootname from `orginfo` WHERE getNums(code,':')=1)p on SUBSTRING(O.code,1,8)=p.rootcode WHERE getNums(o.code,':')=2select * from orginfo as o left join (select code as rootcode,orgname as rootname from `orginfo` WHERE getNums(code,':')=0)p on SUBSTRING(O.code,1,4)=p.rootcode WHERE getNums(o.code,':')=1......语句的结果集合并到一起
CREATE DATABASE `bykvy`;
USE `bykvy`;
DROP TABLE IF EXISTS `orginfo`;
CREATE TABLE `orginfo` (
`id` int(10) NOT NULL auto_increment,
`code` varchar(255) NOT NULL,
`orgflag` tinyint(1) default '0' COMMENT '0 表示直属机关 1 表示分支机构',
`orgname` varchar(40) default NULL,
`orgdesc` text,
PRIMARY KEY (`id`),
UNIQUE KEY `UQ_orgInfo_code` (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;INSERT INTO `orginfo` VALUES (1,'1001',1,'test','test');
INSERT INTO `orginfo` VALUES (2,'1001:001',1,'test1','test1');
INSERT INTO `orginfo` VALUES (3,'1001:001:001',1,'test11','test11');
INSERT INTO `orginfo` VALUES (4,'1001:001:002',1,'test12','test12');
INSERT INTO `orginfo` VALUES (5,'1001:001:003',1,'test13','test13');
INSERT INTO `orginfo` VALUES (6,'1001:001:004',1,'test14','test14');
INSERT INTO `orginfo` VALUES (7,'1001:002',1,'test2','test2');
INSERT INTO `orginfo` VALUES (8,'1001:002:001',1,'test111','test111');
INSERT INTO `orginfo` VALUES (9,'1001:002:002',1,'test112','test112');
INSERT INTO `orginfo` VALUES (10,'1001:002:003',1,'test113','test113');
INSERT INTO `orginfo` VALUES (11,'1001:002:004',1,'test114','test114');
INSERT INTO `orginfo` VALUES (12,'1001:003',1,'test3','test3');
INSERT INTO `orginfo` VALUES (13,'1001:003:001',1,'test1111','test1111');
INSERT INTO `orginfo` VALUES (14,'1001:003:002',1,'test1112','test1112');
INSERT INTO `orginfo` VALUES (15,'1001:003:003',1,'test1113','test1113');
INSERT INTO `orginfo` VALUES (16,'1001:003:004',1,'test1114','test1114');
INSERT INTO `orginfo` VALUES (17,'1001:004',1,'test4','test4');
INSERT INTO `orginfo` VALUES (18,'1001:005',1,'test5','test5');CREATE FUNCTION `getNums`(checkstr VARCHAR(200),separatorStr VARCHAR(10) ) RETURNS int(11)
begin
DECLARE returnVal int default 0;
DECLARE tmpstr VARCHAR(200) default '';
set checkstr = LCASE(LTRIM(rtrim(checkstr)));
set tmpstr = checkstr;
WHILE LENGTH(tmpstr) > 0 DO
if locate(separatorStr,tmpstr) <= 0 then
return returnVal;
end if;
select returnVal+1 into returnVal;
set tmpstr = SUBSTRING(tmpstr,LOCATE(separatorStr,tmpstr)+length(separatorStr)+1);
END WHILE;
return returnVal;
end;
怎么用一条语句查出数据对应的上一级
例如1001:001 1001:002对应的就是1001
1001:001:003 对应的就是1001:001
等等
也就是把select * from orginfo as o left join (select code as rootcode,orgname as rootname from `orginfo` WHERE getNums(code,':')=1)p on SUBSTRING(O.code,1,8)=p.rootcode WHERE getNums(o.code,':')=2select * from orginfo as o left join (select code as rootcode,orgname as rootname from `orginfo` WHERE getNums(code,':')=0)p on SUBSTRING(O.code,1,4)=p.rootcode WHERE getNums(o.code,':')=1......语句的结果集合并到一起
from orginfo a
where instr('1001:002:002',code)>0
and not exists (select code from orginfo where instr('1001:002:002',code)>0 and length(code)>length(a.code))
length(code)-length(replace(code,':',''))
when 1 then
mid(code,1,instr(code,':')-1)
when 2 then
substring_index(code,':',2)
else
code
end as d1
from orginfo
rootcode是有了 但我要能对应上所有的数据
例如如下数据查询的结果应该是INSERT INTO `orginfo` VALUES (1,'1001',1,'test','test');
INSERT INTO `orginfo` VALUES (2,'1001:001',1,'test1','test1');
INSERT INTO `orginfo` VALUES (3,'1001:001:001',1,'test11','test11');id code orgname orgflag orgdesc rootcode rootname rootflag rootdesc
2 1001:001 test1 1 test1 1001 test 1 test
3 1001:001:001 test11 1 test11 1001:001 test1 1 test1获取的结果应该是上面的
是有表的级联查询出来的而不是截取字符串
select *,
case length(a.code)-length(replace(a.code,':',''))
when 2 then
substring_index(a.code,':',2) else '' end as n1,
case length(a.code)-length(replace(a.code,':',''))
when 1 then
mid(code,1,instr(code,':')-1) else '' end as n2
from orginfo a where instr(a.code,':')>0) a
left join orginfo b
on a.n1=b.code or a.n2=b.code自行修改一下格式
INSERT INTO `orginfo` VALUES (2,'1001:001',1,'test1','test1');
INSERT INTO `orginfo` VALUES (3,'1001:001:001',1,'test11','test11');id code orgname orgflag orgdesc rootcode rootname rootflag rootdesc
2 1001:001 test1 1 test1 1001 test 1 test
3 1001:001:001 test11 1 test11 1001:001 test1 1 test1select b.id,b.code,b.orgname,b.orgflag,b.orgdesc,
a.code as parentCode,a.orgname as parentName,a.orgflag as parentflag,a.orgdesc as parentDesc
from orginfo b, orginfo a
where instr(b.code,a.code)>0
and b.code>a.code
and not exists (select code from orginfo where b.code>code and instr(b.code,code)>0 and length(code)>length(a.code));结果id code orgname orgflag orgdesc parentCode parentName parentflag parentDesc
2 1001:001 test1 1 test1 1001 test 1 test
3 1001:001:001 test11 1 test11 1001:001 test1 1 test1
INSERT INTO `orginfo` VALUES (2,'1001:001',1,'test1','test1');
INSERT INTO `orginfo` VALUES (3,'1001:001:001',1,'test11','test11');
INSERT INTO `orginfo` VALUES (4,'1001:001:002',1,'test12','test12');
INSERT INTO `orginfo` VALUES (5,'1001:001:003',1,'test13','test13');
INSERT INTO `orginfo` VALUES (6,'1001:001:004',1,'test14','test14');
INSERT INTO `orginfo` VALUES (7,'1001:002',1,'test2','test2');
INSERT INTO `orginfo` VALUES (8,'1001:002:001',1,'test111','test111');
INSERT INTO `orginfo` VALUES (9,'1001:002:002',1,'test112','test112');
INSERT INTO `orginfo` VALUES (10,'1001:002:003',1,'test113','test113');
INSERT INTO `orginfo` VALUES (11,'1001:002:004',1,'test114','test114');
INSERT INTO `orginfo` VALUES (12,'1001:003',1,'test3','test3');
INSERT INTO `orginfo` VALUES (13,'1001:003:001',1,'test1111','test1111');
INSERT INTO `orginfo` VALUES (14,'1001:003:002',1,'test1112','test1112');
INSERT INTO `orginfo` VALUES (15,'1001:003:003',1,'test1113','test1113');
INSERT INTO `orginfo` VALUES (16,'1001:003:004',1,'test1114','test1114');
INSERT INTO `orginfo` VALUES (17,'1001:004',1,'test4','test4');
INSERT INTO `orginfo` VALUES (18,'1001:005',1,'test5','test5');
select b.id,b.code,b.orgname,b.orgflag,b.orgdesc,
a.code as parentCode,a.orgname as parentName,a.orgflag as parentflag,a.orgdesc as parentDesc
from orginfo b, orginfo a
where instr(b.code,a.code)>0
and b.code>a.code
and not exists (select code from orginfo where b.code>code and instr(b.code,code)>0 and length(code)>length(a.code))id code orgname orgflag orgdesc parentCode parentName parentflag parentDesc
2 1001:001 test1 1 test1 1001 test 1 test
3 1001:001:001 test11 1 test11 1001:001 test1 1 test1
4 1001:001:002 test12 1 test12 1001:001 test1 1 test1
5 1001:001:003 test13 1 test13 1001:001 test1 1 test1
6 1001:001:004 test14 1 test14 1001:001 test1 1 test1
7 1001:002 test2 1 test2 1001 test 1 test
8 1001:002:001 test111 1 test111 1001:002 test2 1 test2
9 1001:002:002 test112 1 test112 1001:002 test2 1 test2
10 1001:002:003 test113 1 test113 1001:002 test2 1 test2
11 1001:002:004 test114 1 test114 1001:002 test2 1 test2
12 1001:003 test3 1 test3 1001 test 1 test
13 1001:003:001 test1111 1 test1111 1001:003 test3 1 test3
14 1001:003:002 test1112 1 test1112 1001:003 test3 1 test3
15 1001:003:003 test1113 1 test1113 1001:003 test3 1 test3
16 1001:003:004 test1114 1 test1114 1001:003 test3 1 test3
17 1001:004 test4 1 test4 1001 test 1 test
18 1001:005 test5 1 test5 1001 test 1 test