有如下数据库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......语句的结果集合并到一起

解决方案 »

  1. select *
    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))
      

  2. select *,case 
    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
      


  3. 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获取的结果应该是上面的
    是有表的级联查询出来的而不是截取字符串
      

  4. select a.*,b.code from (
    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自行修改一下格式
      

  5. 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      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
      

  6. 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');
    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
      

类似问题 »