根据品牌和小类名称取得对应的客户资料（可用）
--根据小类名称和品牌名来取客户信息
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_out_prdtid char(7)
if OBJECT_ID('Tempdb.dbo.#clientids') is not null
begin
print 'Exists Table Tempdb.dbo.#clientids'
end
else
begin
print 'Not Exist Table Tempdb.dbo.#clientids'
create table #clientids(
[orderid] [char](11) not null,
[prdtid] [char](7) not null,
[price] money,
[num] float,
[customer_id] bigint
)
end
declare out_prdtids_Cursor cursor for
select distinct prdtid from #prdtids
open out_prdtids_Cursor
fetch out_prdtids_Cursor into @bpc_out_prdtid
while @@FETCH_STATUS=0
begin
--内部比较开始
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_prdtid=@bpc_out_prdtid
begin
insert into #clientids ([orderid],[prdtid],[price],[num],[customer_id]) values (@bpc_orderid,@bpc_prdtid,@bpc_price,@bpc_num,@bpc_customer_id)
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 out_prdtids_Cursor into @bpc_out_prdtid
end
close out_prdtids_Cursor
deallocate out_prdtids_Cursor
--select distinct customer_id from #clientids
--select sys_customer.*
--from sys_customer where sys_customer.id in (select distinct customer_id from #clientids)
select sys_customer.id as '客户编号',
sys_customer.realname as '客户姓名',
sys_customer.tel as '电话',
sys_customer.mobile as '手机',
[FS_AP_Province].[Province] as '省份',
[FS_AP_City].[City] as '城市',
[FS_AP_Area].area as '区',
sys_customer.[address] as '详细地址',
sys_admin.[admin_real_name] as '服务业务员'
from sys_customer,[FS_AP_Province],[FS_AP_City],[FS_AP_Area],sys_admin
where [FS_AP_Province].[provinceID]=sys_customer.province
and [FS_AP_City].cityID=sys_customer.city
and [FS_AP_Area].[areaID]=sys_customer.area
and sys_admin.admin_name=sys_customer.serviceby
and sys_customer.id in (select distinct customer_id from #clientids)
等同写法：
select [mf_prdtorder].[orderid] as '订单',
[mf_prdtorder].[b_date] as '订单日期',
[FS_MS_Brand].brandname+[FS_MS_Products].ProductTitle as '品牌型号',
[mf_prdtorder_de].[num] as '购买数量',
[mf_prdtorder].[customer_id] as '客户编号',
[sys_customer].realname as '姓名',
[sys_customer].tel as '电话',
[sys_customer].mobile as '手机',
[sys_customer].[address] as '地址'
--[mf_prdtorder].[customer_id] as '客户编号',
--[sys_customer].realname as '姓名',
--[sys_customer].tel as '电话',
--[sys_customer].mobile as '手机',
--[sys_customer].[address] as '地址',
--[mf_prdtorder_de].[prdtid] as '产品编号',
--[mf_prdtorder_de].[num] as '购买数量',
--[mf_prdtorder_de].[price] as '购买价格',
--[FS_MS_Products].ProductTitle as '型号',
--[FS_MS_Products].brandname as '品牌编号',
--[FS_MS_Brand].brandname as '品牌名称'
from [mf_prdtorder],[mf_prdtorder_de],[FS_MS_Products],[sys_customer],[FS_MS_Brand]
where [mf_prdtorder].orderid=[mf_prdtorder_de].order_id
and [FS_MS_Products].[prdtid]=[mf_prdtorder_de].prdtid
and [sys_customer].[id]=[mf_prdtorder].[customer_id]
and [FS_MS_Brand].bid=[FS_MS_Products].brandname
总结：
同样的条件，第二种写法耗时1秒，第一种1分钟！亏大了啊！
