下面两张表,做一张商品销售报表,要求销售的和没有销售的查询出来在一张报表。主要是看basepath字段。他是一个父级和子级的关系。
销售表
ptypeid QTY TOTAL
2 -600.0000 -1800.0000
5 -2.0000 -1600.0000
4 -5.0000 -25000.0000
3 -20.0000 -40.0000
2 20.0000 50.0000
10 -10.0000 -15000.0000
10 10.0000 15000.0000
10 -10.0000 -15000.0000
11 -5.0000 -2500.0000
11 -3.0000 -1800.0000
8 -20.0000 -1600.0000
15 -10.0000 -2860.0000
14 -5.0000 -1400.0000
8 -10.0000 -800.0000
7 -20.0000 -700.0000
9 -10.0000 -980.0000
13 -10.0000 -2580.00001商品表
baseno basepath fullname
1 1 全部商品
2 1-2 可乐
3 1-3 电池
4 1-4 电脑
5 1-5 五粮液
6 1-6 鼠标
7 1-6-7 双飞燕鼠标
8 1-6-8 罗技
9 1-6-9 技嘉
10 1-10 手机
11 1-11 123
12 1-12 电饭煲
13 1-12-13 美的电饭锅
14 1-12-14 苏宁
15 1-12-15 尼泊尔
16 1-16 香烟
17 1-16-17 玉溪
18 1-16-18 骄子
19 1-16-18-19 蓝娇
20 1-16-18-20 红娇
销售表
ptypeid QTY TOTAL
2 -600.0000 -1800.0000
5 -2.0000 -1600.0000
4 -5.0000 -25000.0000
3 -20.0000 -40.0000
2 20.0000 50.0000
10 -10.0000 -15000.0000
10 10.0000 15000.0000
10 -10.0000 -15000.0000
11 -5.0000 -2500.0000
11 -3.0000 -1800.0000
8 -20.0000 -1600.0000
15 -10.0000 -2860.0000
14 -5.0000 -1400.0000
8 -10.0000 -800.0000
7 -20.0000 -700.0000
9 -10.0000 -980.0000
13 -10.0000 -2580.00001商品表
baseno basepath fullname
1 1 全部商品
2 1-2 可乐
3 1-3 电池
4 1-4 电脑
5 1-5 五粮液
6 1-6 鼠标
7 1-6-7 双飞燕鼠标
8 1-6-8 罗技
9 1-6-9 技嘉
10 1-10 手机
11 1-11 123
12 1-12 电饭煲
13 1-12-13 美的电饭锅
14 1-12-14 苏宁
15 1-12-15 尼泊尔
16 1-16 香烟
17 1-16-17 玉溪
18 1-16-18 骄子
19 1-16-18-19 蓝娇
20 1-16-18-20 红娇
create table 销售表(ptypeid int, QTY decimal(10,4), TOTAL decimal(10,4))
insert 销售表
select 2, -600.0000, -1800.0000 union all
select 5, -2.0000, -1600.0000 union all
select 4, -5.0000, -25000.0000 union all
select 3, -20.0000, -40.0000 union all
select 2, 20.0000, 50.0000 union all
select 10, -10.0000, -15000.0000 union all
select 10, 10.0000, 15000.0000 union all
select 10, -10.0000, -15000.0000 union all
select 11, -5.0000 ,-2500.0000 union all
select 11, -3.0000, -1800.0000 union all
select 8, -20.0000 ,-1600.0000 union all
select 15, -10.0000, -2860.0000 union all
select 14, -5.0000 ,-1400.0000 union all
select 8, -10.0000 ,-800.0000 union all
select 7, -20.0000 ,-700.0000 union all
select 9, -10.0000 ,-980.0000 union all
select 13, -10.0000 ,-2580.00001
--drop table 商品表
create table 商品表(baseno int, basepath varchar(20),fullname varchar(20))
insert 商品表
select 1,'1','全部商品' union all
select 2 ,'1-2','可乐' union all
select 3 ,'1-3','电池' union all
select 4 ,'1-4','电脑' union all
select 5 ,'1-5','五粮液' union all
select 6 ,'1-6','鼠标' union all
select 7 ,'1-6-7','双飞燕鼠标' union all
select 8 ,'1-6-8','罗技' union all
select 9 ,'1-6-9','技嘉' union all
select 10 ,'1-10','手机' union all
select 11 ,'1-11','123' union all
select 12 ,'1-12','电饭煲' union all
select 13 ,'1-12-13','美的电饭锅' union all
select 14 ,'1-12-14','苏宁' union all
select 15 ,'1-12-15','尼泊尔' union all
select 16 ,'1-16','香烟' union all
select 17 ,'1-16-17','玉溪' union all
select 18 ,'1-16-18','骄子' union all
select 19 ,'1-16-18-19','蓝娇' union all
select 20 ,'1-16-18-20','红娇'
select max(a.fullname)商品名字,isnull(a.basepath,b.basepath)BasePath,sum(c.QTY)数量,sum(c.total)金额
--select a.fullname,isnull(a.basepath,b.basepath)BasePath,c.QTY,total,b.baseno
from 商品表 a
left join 商品表 b
on len(a.basepath)<len(b.basepath)
and substring(b.basepath,1,len(a.basepath))=a.basepath
left join 销售表 c
on c.ptypeid=isnull(b.baseno,a.baseno)
group by isnull(a.basepath,b.basepath)
order by max(a.baseno)/*
商品名字 BasePath 数量 金额
-------------------- -------------------- ---------------------------------------- --------------------
全部商品 1 -710.0000 -58610.0000
可乐 1-2 -580.0000 -1750.0000
电池 1-3 -20.0000 -40.0000
电脑 1-4 -5.0000 -25000.0000
五粮液 1-5 -2.0000 -1600.0000
鼠标 1-6 -60.0000 -4080.0000
双飞燕鼠标 1-6-7 -20.0000 -700.0000
罗技 1-6-8 -30.0000 -2400.0000
技嘉 1-6-9 -10.0000 -980.0000
手机 1-10 -10.0000 -15000.0000
123 1-11 -8.0000 -4300.0000
电饭煲 1-12 -25.0000 -6840.0000
美的电饭锅 1-12-13 -10.0000 -2580.0000
苏宁 1-12-14 -5.0000 -1400.0000
尼泊尔 1-12-15 -10.0000 -2860.0000
香烟 1-16 NULL NULL
玉溪 1-16-17 NULL NULL
骄子 1-16-18 NULL NULL
蓝娇 1-16-18-19 NULL NULL
红娇 1-16-18-20 NULL NULL
*/
set @BasePath='1'
select * from (
select max(a.fullname)商品名字,isnull(a.basepath,b.basepath)BasePath,sum(c.QTY)数量,sum(c.total)金额
--select a.fullname,isnull(a.basepath,b.basepath)BasePath,c.QTY,total,b.baseno
from 商品表 a
left join 商品表 b
on len(a.basepath)<len(b.basepath)
and substring(b.basepath,1,len(a.basepath))=a.basepath
left join 销售表 c
on c.ptypeid=isnull(b.baseno,a.baseno)
group by isnull(a.basepath,b.basepath)
) tb where BasePath= @BasePath/*
商品名字 BasePath 数量 金额
-------------------- -------------------- ---------------------------------------- ---
全部商品 1 -710.0000 -58610.0000
*/