想要知道按车型品牌分类查询出第一层的车的销售数量
表1为汽车的分类表
表2为汽车的销售数量
drop table PRODUCT_CAR;
create table PRODUCT_CAR(Code varchar(100),Description varchar(100),parent_id varchar(20))
insert into PRODUCT_CAR select * from (
select '0' as code, 'car' as Description,'' as parent_id union all
select '1' as code, 'BMW' as Description,'0' as parent_id union all
select '2' as code, 'BENZ' as Description,'0' as parent_id union all
select '3' as code, 'VW' as Description,'0' as parent_id union all
select '4' as code, 'Hummer' as Description,'0' as parent_id union all
select 'B5' as code, 'BMW 5 series' as Description,'1' as parent_id union all
select 'B6' as code, 'BMW 6 series' as Description,'1' as parent_id union all
select 'B7' as code, 'BMW 7 series' as Description,'1' as parent_id union all
select 'Z1' as code, 'BZ Home Series' as Description,'3' as parent_id union all
select 'Z2' as code, 'BZ Sercial series' as Description,'3' as parent_id union all
select '1A' as code, 'BZ A series' as Description,'Z1' as parent_id union all
select '1B' as code, 'BZ B series' as Description,'Z1' as parent_id union all
select 'ZS' as code, 'BZ S series' as Description,'Z2' as parent_id union all
select 'V1' as code, 'VW Home Series' as Description,'ZS' as parent_id union all
select 'V2' as code, 'vw Sercial series' as Description,'3' as parent_id union all
select 'V3' as code, 'AUDIO Sercial series' as Description,'3' as parent_id union all
select 'V001' as code, 'VW SUVS series' as Description,'V1' as parent_id union all
select 'VA01' as code, 'AD HOME Sercial' as Description,'V3' as parent_id union all
select 'VA02' as code, 'AD SUVS Sercial' as Description,'V3' as parent_id union all
select 'VA21' as code, 'AD A Sercial' as Description,'V3' as parent_id union all
select 'VA211' as code, 'AD Q Sercial' as Description,'VA02' as parent_id
) a;
SELECT * FROM PRODUCT_CAR ORDER BY CODE ASC;
taBLE 2
create table SEll_CAR(
Car_Code varchar(100),
Description varchar(100),
quantity numeric(10),
product_code varchar(20)
);
insert into sell_car select * from (
select 'B53001' as Car_code , 'BMW 535 LI luxury cars' as Description, '21' as quantity, 'B5' as product_code union all
select 'B75001' as Car_code , 'BMW 750 LI XDriver' as Description, '200' as quantity, 'B7' as product_code union all
select '21101' as Car_code , 'AD Q7 luxury cars' as Description, '30' as quantity, 'Va211' as product_code union all
select 'VA211' as Car_code , 'AD A6 LI luxury cars' as Description, '2' as quantity, 'VA01' as product_code union all