sql 多级内查询
最近在开发一个外包的项目,其中有个需求,一直困扰我好几天,今天终于找到了解决方案。大致需求如下:公司总部发货给经销商,其中经销商包含四种级别,钻石、金牌、银牌和铜牌,等级依次下发,钻石包含金牌,金牌包含银牌,银牌包含铜牌,铜牌下面有终端客户,也就是产品的终端,给客户使用。
如果系统登陆的是钻石经销商,需要查询他的客户,需要把最底层的经销商查出来,最后,查询所属的终端客户。
大致的存储过程:
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
最新文章
- CSS 高级布局技巧
- Spring学习记录(七)---表达式语言-SpEL
- 11月6日下午PHP注册审核(审核状态控制登录、可以更改审核状态)
- 删除smartygit的配置文件
- WCF服务创建与使用(双工模式)
- AVL树(二)之 C++的实现
- MUI - 图片预览(perviewimage)的优化
- 用Python脚本做一些网页游戏中力所能及的自动化任务
- Hadoop集群(第10期)_MapReduce与MySQL交互
- 使用ffmpeg实现转码样例(代码实现)
- JQuery - 垂直显示隐藏DIV
- CURL访问url显示响应时间
- git分支管理之创建与合并分支
- 5.分析内核中断运行过程,以及中断3大结构体:irq_desc、irq_chip、irqaction
- 使用Python实现的杨辉三角
- gulp填坑记(二)——gulp多张图片自动合成雪碧图
- beego笔记
- java之异常统一处理
- Linux安装Broadcom无线驱动
- &#39;QuerySet&#39; object has no attribute &#39;_meta&#39;