按大类拼接数据，递归查询请教
本帖最后由 mfkpie 于 2013-09-12 00:46:58 编辑 想要知道按车型品牌分类查询出第一层的车的销售数量
表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;
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
select 'VW1001' as Car_code , 'VW Cross polo ' as Description, '100' as quantity, 'V1' as product_code
) b;
select * from sell_car;
PRODUCT_CAR.code PRODUCT_CAR.Description sell.Sell_SUM递归 树状 树型 拼接
1 BMW 221
2 BENZ 0
3 vw 132
4 Hummer 0
with car (Code ,[Description],parent_id) as
(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 ),
sell(Car_Code,[Description],quantity,product_code ) as
(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
select 'VW1001' as Car_code , 'VW Cross polo ' as Description, '100' as quantity, 'V1'),
test as
(select a.Code,a.[Description],b.Quantity,a.Parent_id from car a,sell b where b.product_code=a.code
union all
select b.Code,b.[Description],a.Quantity, b.Parent_id from test a,car b where a.Parent_id=b.Code)
select a.Code,a.[Description],ISNULL(b.Qty,0)Qty from car a left join
(select code,sum(convert(int,quantity)) qty from test group by code) b
on a.code=b.code where a.Parent_id='0'
order by a.Code
/*
Code Description Qty
----- -------------------- -----------
1 BMW 221
2 BENZ 0
3 VW 132
4 Hummer 0
*/
