有个稍微复杂的查询写不出来，求救

[hellocplanguage]

这是表2

这是表1

表1要联合表2查询，把 数量汇总，对应的material_id的产品，汇总qty数量，要对应LOP、WLD、VF三个属性的
汇总成类似这种

material_id, material_name, material_model, sum(qty), material_unit, lop, wld, vf
01.01.01.001.0001 dice 芯片 155.9 千个 0-100 0-200 0-3

以上，其实就是各种组合（档次），求汇总数量，坑爹的需求，原谅我的菜鸟思维，这已经是我搞出来的中间表了，头发快掉光了

[hellocplanguage]

CREATE TABLE `study`.`test_master`( `id` INT NOT NULL, `material_id` VARCHAR(255) NOT NULL, `material_name` VARCHAR(255) NOT NULL, `material_model` VARCHAR(255) NOT NULL, `qty` DOUBLE NOT NULL, `material_unit` VARCHAR(64) NOT NULL, `lop` DOUBLE NOT NULL, `wld` DOUBLE NOT NULL, `vf` DOUBLE NOT NULL, PRIMARY KEY (`id`) ); 这是表1的列 INSERT INTO `study`.`test_master` (`material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) VALUES ('01.01.01.010.0001', 'dice', '芯片', '42.5', '千个', '300', '400', '5'); INSERT INTO `study`.`test_master` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) VALUES (NULL, '01.01.01.010.0001', 'dice', '芯片', '42.5', '千个', '66', '100', '2'); INSERT INTO `study`.`test_master` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) VALUES (NULL, '01.01.01.010.0001', 'dice', '芯片', '55.6', '千个', '66', '100', '2'); INSERT INTO `study`.`test_master` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) VALUES (NULL, '01.01.01.010.0001', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `study`.`TEST_MaSTER` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `study`.`test_master` (`id`, `mATERIAl_ID`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `study`.`test_master` (`id`, `material_id`, `material_NAME`, `MAterial_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `study`.`test_master` (`id`, `material_id`, `material_name`, `material_modEL`, `qTY`, `material_unit`, `lop`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); INSert into `study`.`test_master` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_UNIT`, `LOp`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `STUDY`.`TESt_master` (`id`, `material_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `VF`) VALUes (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); insert into `study`.`test_master` (`ID`, `MATerial_id`, `material_name`, `material_model`, `qty`, `material_unit`, `lop`, `wld`, `vf`) values (NULL, '01.01.01.010.0002', 'dice', '芯片', '66.8', '千个', '255', '425.5', '8'); CREATE TABLE `study`.`test_asstattr`( `id` INT NOT NULL, `material_id` VARCHAR(255) NOT NULL, `asstattr_id` VARCHAR(16) NOT NULL, `level_standard` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ); 这是表2的列 INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'lop', '0-100'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'lop', '101-150'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'lop', '151-200'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'lop', '201-600'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'lop', '601-999'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'vf', '0-3'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'vf', '4-8'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'wld', '0-200'); INSERT INTO `study`.`test_asstattr` (`material_id`, `asstattr_id`, `level_standard`) VALUES ('01.01.01.010.0001', 'wld', '201-400'); insert into `study`.`TEST_AsSTATtr` (`material_id`, `asstattr_id`, `level_standard`) values ('01.01.01.010.0001', 'wld', '401-600');

[ckc]

这个结构有点混乱 建议表1增加几个字段，比如loplevel，取值范围是0-100,101-150，。。。这样，根据lop的实际值更新这个字段 然后就可以用group by loplevel来进行统计出结果了

[chengccy]

表连接+group by

WITH TMP AS
 (SELECT MATERIAL_ID, ASSTATTR_ID, LEVEL_STANDARD,
         REGEXP_SUBSTR(LEVEL_STANDARD, '[^-]+', 1, 1) AS S,
         REGEXP_SUBSTR(LEVEL_STANDARD, '[^-]+', 1, 2) AS E
  FROM   TEST_ASSTATTR)
SELECT T.MATERIAL_ID, T.MATERIAL_NAME, T.MATERIAL_MODEL, SUM(T.QTY),
       T.MATERIAL_UNIT, T1.LEVEL_STANDARD AS LOP, T2.LEVEL_STANDARD AS VF,
       T3.LEVEL_STANDARD AS WLD
FROM   TEST_MASTER T
LEFT   JOIN TMP T1 -- lop
ON     T1.MATERIAL_ID = T.MATERIAL_ID
AND    T1.ASSTATTR_ID = 'lop'
AND    T.LOP BETWEEN T1.S AND T1.E
LEFT   JOIN TMP T2 -- vf
ON     T2.MATERIAL_ID = T.MATERIAL_ID
AND    T2.ASSTATTR_ID = 'vf'
AND    T.VF BETWEEN T2.S AND T2.E
LEFT   JOIN TMP T3 -- wld
ON     T3.MATERIAL_ID = T.MATERIAL_ID
AND    T3.ASSTATTR_ID = 'wld'
AND    T.WLD BETWEEN T3.S AND T3.E
GROUP  BY T.MATERIAL_ID, T.MATERIAL_NAME, T.MATERIAL_MODEL, T.MATERIAL_UNIT,
          T1.LEVEL_STANDARD, T2.LEVEL_STANDARD, T3.LEVEL_STANDARD
ORDER  BY T.MATERIAL_ID, T1.LEVEL_STANDARD, T2.LEVEL_STANDARD, T3.LEVEL_STANDARD

[jdsnhan]

你给的示例数据有缺陷。lop,wld,vf三个标准是否可总匹配？ 比如说，lop 0-100的，wld 0-200的，vf 0-3的，总是同时出现？ 如果是，可以先把master表的内容匹配到辅助表的级别，然后再分组显示。 参照：

select m.*,a.rn ,a.level_standard,a.asstattr_id from test_master m,
(select rownum rn ,a.material_id,a.asstattr_id,level_standard, substr(a.level_standard,1,instr(a.level_standard,'-')-1) minlevel,substr(a.level_standard,instr(a.level_standard,'-') + 1) maxlevel from test_asstattr a) a
where m.material_id = a.material_id 
and m.lop between a.minlevel and a.maxlevel

[hellocplanguage]

引用 3 楼 chengccy 的回复:

表连接+group by

WITH TMP AS
 (SELECT MATERIAL_ID, ASSTATTR_ID, LEVEL_STANDARD,
         REGEXP_SUBSTR(LEVEL_STANDARD, '[^-]+', 1, 1) AS S,
         REGEXP_SUBSTR(LEVEL_STANDARD, '[^-]+', 1, 2) AS E
  FROM   TEST_ASSTATTR)
SELECT T.MATERIAL_ID, T.MATERIAL_NAME, T.MATERIAL_MODEL, SUM(T.QTY),
       T.MATERIAL_UNIT, T1.LEVEL_STANDARD AS LOP, T2.LEVEL_STANDARD AS VF,
       T3.LEVEL_STANDARD AS WLD
FROM   TEST_MASTER T
LEFT   JOIN TMP T1 -- lop
ON     T1.MATERIAL_ID = T.MATERIAL_ID
AND    T1.ASSTATTR_ID = 'lop'
AND    T.LOP BETWEEN T1.S AND T1.E
LEFT   JOIN TMP T2 -- vf
ON     T2.MATERIAL_ID = T.MATERIAL_ID
AND    T2.ASSTATTR_ID = 'vf'
AND    T.VF BETWEEN T2.S AND T2.E
LEFT   JOIN TMP T3 -- wld
ON     T3.MATERIAL_ID = T.MATERIAL_ID
AND    T3.ASSTATTR_ID = 'wld'
AND    T.WLD BETWEEN T3.S AND T3.E
GROUP  BY T.MATERIAL_ID, T.MATERIAL_NAME, T.MATERIAL_MODEL, T.MATERIAL_UNIT,
          T1.LEVEL_STANDARD, T2.LEVEL_STANDARD, T3.LEVEL_STANDARD
ORDER  BY T.MATERIAL_ID, T1.LEVEL_STANDARD, T2.LEVEL_STANDARD, T3.LEVEL_STANDARD

谢谢大大，万分感谢！！

[hellocplanguage]

引用 2 楼 ckc 的回复:

这个结构有点混乱 建议表1增加几个字段，比如loplevel，取值范围是0-100,101-150，。。。这样，根据lop的实际值更新这个字段 然后就可以用group by loplevel来进行统计出结果了

我也想啊，可是不能啊，这两个表也是中间表，我拼出来的

[hellocplanguage]

引用 4 楼 jdsnhan 的回复:

你给的示例数据有缺陷。lop,wld,vf三个标准是否可总匹配？ 比如说，lop 0-100的，wld 0-200的，vf 0-3的，总是同时出现？ 如果是，可以先把master表的内容匹配到辅助表的级别，然后再分组显示。 参照：

select m.*,a.rn ,a.level_standard,a.asstattr_id from test_master m,
(select rownum rn ,a.material_id,a.asstattr_id,level_standard, substr(a.level_standard,1,instr(a.level_standard,'-')-1) minlevel,substr(a.level_standard,instr(a.level_standard,'-') + 1) maxlevel from test_asstattr a) a
where m.material_id = a.material_id 
and m.lop between a.minlevel and a.maxlevel

这个不一定，就是各种档次例如wld 3个阶段，lop 3个阶段，vf 3个阶段，然后有3*3*3=27个组合出来，分别统计（可用）数量，也就是说同一个material_id的东西（产品），同一个属性有可能有多种范围，现在就是要查询每个组合有的数量，没有数量的留空