最近在开发一个外包的项目，其中有个需求，一直困扰我好几天，今天终于找到了解决方案。大致需求如下：公司总部发货给经销商，其中经销商包含四种级别，钻石、金牌、银牌和铜牌，等级依次下发，钻石包含金牌，金牌包含银牌，银牌包含铜牌，铜牌下面有终端客户，也就是产品的终端，给客户使用。
如果系统登陆的是钻石经销商，需要查询他的客户，需要把最底层的经销商查出来，最后，查询所属的终端客户。
大致的存储过程：
USE [DbDevice]
GO
/****** Object: StoredProcedure [dbo].[Pro_FindMaterialByTerminalId] Script Date: 07/14/2014 14:21:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Pro_FindMaterialByTerminalId]
@DealerId varchar(50),
@MaterialName varchar(50),
@StartDate varchar(50),
@EndDate varchar(50)
AS
BEGIN
SET NOCOUNT ON;
declare @where varchar(1000)
declare @sql varchar(1000)
declare @select varchar(800)
declare @order varchar(100)
declare @type int
set @where=' '
set @sql=''
set @select=''
set @order=''
set @type=(select t.Level from Tbl_Dealer t where t.DealerId=@DealerId)
if(@MaterialName<>'') set @where=@where+' And A1.SerialNumber='+@MaterialName
set @where=@where+' And A1.UseTime>='''+@StartDate+''' And A1.UseTime<='''+@EndDate+''''
if(@type=0)
begin
set @select=' select A1.TypeId,A1.SerialNumber,A1.CreateTime,B1.TerminalName,A1.Barcode,A1.ProvinceId,A1.DeviceId,A1.UseTime, A1.Status from Tbl_Material A1 ,Tbl_Terminal B1
where A1.TerminalId=B1.TerminalId And A1.TerminalId in (select A.TerminalID
from Tbl_Terminal A
where A.DealerId in (select H.DealerId From Tbl_Dealer H where H.ParentId in(
select G.DealerId From Tbl_Dealer G where G.ParentId in(
select F.DealerId From Tbl_Dealer F where F.ParentId in
(select E.DealerId from Tbl_Dealer E where E.DealerId='''+@DealerId+''')))))'
end
if(@type=1)
begin
set @select=' select A.TerminalID
from Tbl_Terminal A
where A.DealerId in (
select G.DealerId From Tbl_Dealer G where G.ParentId in(
select F.DealerId From Tbl_Dealer F where F.ParentId in
(select E.DealerId from Tbl_Dealer E where E.DealerId='''+@DealerId+''')))'
end
if(@type=2)
begin
set @select=' select A.TerminalID
from Tbl_Terminal A
where A.DealerId in (
select F.DealerId From Tbl_Dealer F where F.ParentId in
(select E.DealerId from Tbl_Dealer E where E.DealerId='''+@DealerId+'''))'
end
if(@type=3)
begin
set @select=' select A.TerminalID
from Tbl_Terminal A
where A.DealerId in (
select E.DealerId from Tbl_Dealer E where E.DealerId='''+@DealerId+''')'
end
set @order=' order by A1.UseTime desc'
set @sql=@select+@where+@order
print @sql
exec (@sql)
END