--/*产品表*/;
--create table tb_product
--(
-- pr_id char(10) not null,
-- pr_no char(10) not null,
-- pr_name char(10) not null,
-- pr_price float(2) not null,
-- pr_fatherid char(10) not null,
-- pr_isleaf char(10) not null
--);
--ALTER TABLE tb_product ADD PRIMARY KEY (pr_id);
---- select * from tb_order
--/*订单表*/;
--create table tb_order
--(
-- or_no char(10) not null,
-- pr_id char(10) not null,
-- or_quanity int not null,
-- pe_id varchar(10) not null
--);
--ALTER TABLE tb_order ADD PRIMARY KEY (or_no);
---- select * from tb_period
--/*周期表*/;
--create table tb_period
--(
-- pe_id char(10) not null,
-- pe_order int not null,
-- pe_startdate datetime,
-- pe_enddate datetime
--);
--ALTER TABLE tb_period ADD PRIMARY KEY (pe_id);
--insert into tb_period values('GUIDA',1,'2018-01-01','2018-01-15');
--insert into tb_period values('GUIDB',2,'2018-01-16','2018-01-31');
--insert into tb_order values('N001','3',2,'GUIDA');
--insert into tb_order values('N002','4',3,'GUIDB');
--insert into tb_order values('N003','6',2,'GUIDA');
--insert into tb_order values('N004','7',3,'GUIDB');
--insert into tb_order values('N005','3',2,'GUIDA');
--insert into tb_product values('1','a','电子设备',0,-1,0);
--insert into tb_product values('2','b','pc',0,1,0);
--insert into tb_product values('3','c','联想',4399,2,1);
--insert into tb_product values('4','d','戴尔',4799,2,1);
--insert into tb_product values('5','e','手机',0,1,0);
--insert into tb_product values('6','f','苹果',8099,5,1);
--insert into tb_product values('7','g','华为',6500,5,1);
知识点：
动态sql
临时表
with CET as （递归）
FOR XML
行转列
join
union all
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#temprsource') and type='U')
drop table #temprsource
declare @event xml,@sql varchar(4000)='',@clum varchar(4000)='',@qu varchar(4000)='',@total varchar(4000)=''
set @event =( select pe_id+',' from tb_order b group by pe_id FOR XML PATH(''));
set @qu =( select 'isnull( sum( '+ pe_id+'),0) as '+pe_id+'数量,' from tb_order b group by pe_id FOR XML PATH(''));
set @total =( select 'isnull( sum( '+ pe_id+'),0) as '+pe_id+'金额,' from tb_order b group by pe_id FOR XML PATH(''));
set @qu= stuff(@qu,len(@qu),1,'');
set @total= stuff(@total,len(@total),1,'');
set @clum= stuff(convert(nvarchar,@event),len(convert(nvarchar,@event)),1,'');
--select @qu;
-- select @total;
-- select @clum;
set @sql=
N'select * from
(
select pr_id,pr_name, '+@qu+' from #temprsource
PIVOT
(
sum(or_quanity) FOR
pe_id IN ('+@clum+')
) p1 group by pr_id,pr_name) q1
inner join
(
select pr_id,pr_name, '+@total+' from #temprsource
PIVOT
(
sum(pr_price) FOR
pe_id IN ('+@clum+')
) p2 group by pr_id,pr_name
) q2
on q1.pr_id =q2.pr_id
';
--print(@sql);
with orgin as
(
select po.pr_id, pr_no,pr_name,pr_price,or_no,or_quanity,pe_id,pr_fatherid,pr_isleaf from tb_product po left join tb_order o on po.pr_id=o.pr_id
)
,
cte as
(
select l.pr_id,l.pr_name,l.pr_fatherid,isnull(l.or_quanity,0) as or_quanity ,l.pe_id,l.pr_price as pr_price from orgin as L where L.pr_isleaf=1
union all
select a.pr_id,a.pr_name,a.pr_fatherid, ( isnull( a.or_quanity,0)+isnull(b.or_quanity,0 ))as or_quanity , b.pe_id,b.pr_price as pr_price from orgin as a join cte b
on a.pr_id=b.pr_fatherid
)
,
main as
(
select * from (
select pr_id,pr_name,pe_id, sum(or_quanity) as or_quanity, sum(or_quanity*pr_price) as pr_price from
(select pr_id,pr_name,pe_id,sum(or_quanity) as or_quanity ,pr_price from cte group by pr_id,pr_name,pe_id,pr_price) as g
group by pr_id,pr_name,pe_id)
AS s
)
select * into #temprsource from main
exec(@sql)
