--根据小类名称和品牌名来取客户信息
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%"