根据品牌和产品小类分析客户信息

--根据小类名称和品牌名来取客户信息
declare @brandname nvarchar(50)
declare @categoryname nvarchar(50) ---分类的中文名

 

set @categoryname='空调'
set @brandname='海尔'

--取得ClassID
declare @categoryid nvarchar(15)
if OBJECT_ID('Tempdb.dbo.#categoryids') is not null
begin
 print 'Exists Table Tempdb.dbo.#categoryids'
end
else
begin
 print 'Not Exists Table Tempdb.dbo.#categoryids'
 create Table #categoryids(
  [categoryid] [nvarchar](15) not null
 )
end
declare categoryidsCursor cursor for
 select ClassID from FS_MS_ProductsClass where ClassCName=@categoryname
open categoryidsCursor
fetch categoryidsCursor into @categoryid
while @@FETCH_STATUS=0
begin
 insert into #categoryids (categoryid) values (@categoryid)
 fetch next from categoryidsCursor into @categoryid
end
close categoryidsCursor
deallocate categoryidsCursor
--select distinct * from #categoryids

 

--取得品牌ID
declare @brandid char(3)
if OBJECT_ID('Tempdb.dbo.#brandids') is not null
begin
 print 'Exists Table Tempdb.dbo.#brandids'
 --drop table #categoryids
end
else
begin
 print 'Not Exists Table Tempdb.dbo.#brandids'
 create table #brandids(
  [bid] [char](3) not null
 )
end
declare brandidsCursor cursor for
 select bid from FS_MS_Brand where brandname=@brandname
open brandidsCursor
fetch brandidsCursor into @brandid
while @@FETCH_STATUS=0
begin
 insert into #brandids (bid) values (@brandid)
 fetch next from brandidsCursor into @brandid
end
close brandidsCursor
deallocate brandidsCursor
--select distinct * from #brandids

 

declare @bid char(3)
declare @classid nvarchar(15)
declare @prdtid char(7)
if OBJECT_ID('Tempdb.dbo.#prdtids') is not null
begin
 print 'Exists Table Tempdb.dbo.#prdtids'
end
else
begin
 print 'Mot Exists Table Tempdb.dbo.#prdtids'
 create table #prdtids(
  [prdtid] [char](7) not null
 )
end
declare brandidCursor cursor for
 select distinct bid from #brandids
open brandidCursor
fetch brandidCursor into @bid
while @@FETCH_STATUS=0
begin
 --对这个品牌各个产品小类进行产品筛选开始
 declare classidCursor cursor for
  select distinct categoryid from #categoryids
 open classidCursor
 fetch classidCursor into @classid
 while @@FETCH_STATUS=0
 begin
  --对小分类进行筛选开始
  IF CURSOR_STATUS('global','prdtidInnerCusor')>=-1
  BEGIN
   DEALLOCATE prdtidInnerCusor
  END
  declare prdtidInnerCusor cursor for
   select prdtid from FS_MS_Products where ClassID=@classid and brandname=@bid
  open prdtidInnerCusor
  fetch prdtidInnerCusor into @prdtid
  while @@FETCH_STATUS=0
  begin
   insert into #prdtids (prdtid) values (@prdtid)
   fetch next from prdtidInnerCusor into @prdtid
  end
  close prdtidInnerCusor
  deallocate prdtidInnerCusor
  --结束
  fetch next from classidCursor into @classid
 end
 close classidCursor
 deallocate classidCursor
 --筛选结束
 fetch next from brandidCursor into @bid
end
close brandidCursor
deallocate brandidCursor
select distinct prdtid from #prdtids

 

 

declare @bpc_orderid char(11)
declare @bpc_prdtid char(7)
declare @bpc_price money
declare @bpc_num float
declare @bpc_customer_id bigint
declare @bpc_inner_prdtid char(7)
declare @bpc_tablename nvarchar(20)
declare @generateTemporaryTableSql nvarchar(4000)
declare @generateTemporaryInnerSql nvarchar(4000)
declare inner_prdtids_Cursor cursor for
 select distinct prdtid from #prdtids
open inner_prdtids_Cursor
fetch inner_prdtids_Cursor into @bpc_inner_prdtid
while @@FETCH_STATUS=0
begin
 set @bpc_tablename='#clientids'+@bpc_inner_prdtid
 --set @generateTemporaryTableSql=N'if OBJECT_ID(''Tempdb.dbo.'+@bpc_tablename+') is not null
 --begin
 -- print ''Exists Table Tempdb.dbo.'+@bpc_tablename+'
 --end
 --else
 --begin
 -- create table '+@bpc_tablename+'(
 --  [orderid] [char](11) not null,
 --  [prdtid] [char](7) not null,
 --  [price] money,
 --  [num] float,
 --  [customer_id] bigint
 -- )
 --end'
 set @generateTemporaryTableSql=N'if OBJECT_ID(''Tempdb.dbo.'+@bpc_tablename+''') is  null begin create table '+@bpc_tablename+'([orderid] [char](11) not null,[prdtid] [char](7) not null,[price] money,[num] float,[customer_id] bigint) end'
 exec(@generateTemporaryTableSql)
 --内部比较开始
 declare bpcTotalCursor cursor for
 select mf_prdtorder_de.order_id,
 mf_prdtorder_de.prdtid,
 mf_prdtorder_de.price,
 mf_prdtorder_de.num,
 mf_prdtorder.customer_id
 FROM [mf_prdtorder_de],mf_prdtorder where mf_prdtorder.orderid=mf_prdtorder_de.order_id
 open bpcTotalCursor
 fetch bpcTotalCursor into @bpc_orderid,@bpc_prdtid,@bpc_price,@bpc_num,@bpc_customer_id
 while @@FETCH_STATUS=0
 begin
  --
  if @bpc_orderid=@bpc_inner_prdtid
  begin
   --set @generateTemporaryInnerSql=N'insert into '+@bpc_tablename+' (
   --[orderid],[prdtid],[price],[num],[customer_id]) values (@bpc_orderid,@bpc_prdtid,@bpc_price,@bpc_num,@bpc_customer_id)'
   set @generateTemporaryInnerSql=N'insert into '+@bpc_tablename+' ([orderid],[prdtid],[price],[num],[customer_id]) values (@bpc_orderid,@bpc_prdtid,@bpc_price,@bpc_num,@bpc_customer_id)'
   exec(@generateTemporaryInnerSql)
  end
  --
  fetch next from bpcTotalCursor into @bpc_orderid,@bpc_prdtid,@bpc_price,@bpc_num,@bpc_customer_id
 end
 close bpcTotalCursor
 deallocate bpcTotalCursor
 --内部比较结束
 fetch next from inner_prdtids_Cursor into @bpc_inner_prdtid
end
close inner_prdtids_Cursor
deallocate inner_prdtids_Cursor

EXEC sp_tables "#clientids%"