当前位置：首页 → 问答吧 → 求高人解析SQL语句

求高人解析SQL语句

时间：2011-11-28

来源：互联网

SQL code

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P') DROP PROC user_RecordInResult
GO
Create PROCEDURE user_RecordInResult
@charWhere nvarchar(1000)

AS
declare @sql varchar(8000)
Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int
declare @sInvCode varchar(50),@eInvCode varchar(50)
if object_id('tempdb..resultTemp') is not null drop table tempdb..resultTemp

if LTRIM(RTRIM(@charwhere))<>''
begin 
    SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10
    SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10

    IF @B1>10 SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1)
    IF @B2>10 SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2)
end

select cInvName,cInvStd,cComUnitName ,
case when mon=1 then sum(isum) else 0 end '1月出',case when mon=2 then sum(isum) else 0 end '2月出',
case when mon=3 then sum(isum) else 0 end '3月出',case when mon=4 then sum(isum) else 0 end '4月出',
case when mon=5 then sum(isum) else 0 end '5月出',case when mon=6 then sum(isum) else 0 end '6月出',
case when mon=7 then sum(isum) else 0 end '7月出',case when mon=8 then sum(isum) else 0 end '8月出',
case when mon=9 then sum(isum) else 0 end '9月出',case when mon=10 then sum(isum) else 0 end '10月出',
case when mon=11 then sum(isum) else 0 end '11月出',case when mon=12 then sum(isum) else 0 end '12月出'
into  tempdb..resultTemp
from(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon
from rdrecord r join rdrecords s on r.id=s.id
left join Inventory i on i.cInvcode=s.cInvCode
left join computationUnit u on u.cComUnitCode=i.cComUnitCode
where r.bRdFlag=0 and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0))
group by i.cInvName,i.cInvStd,u.cComUnitName,r.dDate ) a
group by cInvName,cInvStd,cComUnitName,mon

每句所展现的意义是什么意思？

虽然有点难度...先谢谢了....

作者: y516940914 发布时间: 2011-11-28

这人也太懒了。临时表前面也不舍得加个#

是不是这样写看着更帅呢？

作者: Beirut 发布时间: 2011-11-28

把条件字符串切割出来了，这个传参纯粹是吃饱了撑的。

作者: ssp2009 发布时间: 2011-11-28

看不懂

作者: lwc6113108 发布时间: 2011-11-28

SQL code


IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P') DROP PROC user_RecordInResult
GO
--判断该存储过程是否存在
Create PROCEDURE user_RecordInResult
@charWhere nvarchar(1000)

AS
declare @sql varchar(8000)
Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int
declare @sInvCode varchar(50),@eInvCode varchar(50)
if object_id('tempdb..resultTemp') is not null drop table tempdb..resultTemp

if LTRIM(RTRIM(@charwhere))<>''
begin 
    SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10
    SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10

    IF @B1>10 SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1)
    IF @B2>10 SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2)
end

--if else 传入参数的处理

select cInvName,cInvStd,cComUnitName ,
case when mon=1 then sum(isum) else 0 end '1月出',case when mon=2 then sum(isum) else 0 end '2月出',
case when mon=3 then sum(isum) else 0 end '3月出',case when mon=4 then sum(isum) else 0 end '4月出',
case when mon=5 then sum(isum) else 0 end '5月出',case when mon=6 then sum(isum) else 0 end '6月出',
case when mon=7 then sum(isum) else 0 end '7月出',case when mon=8 then sum(isum) else 0 end '8月出',
case when mon=9 then sum(isum) else 0 end '9月出',case when mon=10 then sum(isum) else 0 end '10月出',
case when mon=11 then sum(isum) else 0 end '11月出',case when mon=12 then sum(isum) else 0 end '12月出'
into  tempdb..resultTemp
from(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon
from rdrecord r join rdrecords s on r.id=s.id
left join Inventory i on i.cInvcode=s.cInvCode
left join computationUnit u on u.cComUnitCode=i.cComUnitCode
where r.bRdFlag=0 and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0))
group by i.cInvName,i.cInvStd,u.cComUnitName,r.dDate ) a
group by cInvName,cInvStd,cComUnitName,mon 

--将查询结果按行转列的形式存入临时表

作者: AcHerat 发布时间: 2011-11-28

主要是中间的部分不理解，还请高人指点....

作者: y516940914 发布时间: 2011-11-28

大概解釋一下，把有誤的，樓主自己改改
SQL code

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P')--判斷存儲過程 user_RecordInResult是否存在
 DROP PROC user_RecordInResult --存在時執行刪除
GO
Create PROCEDURE user_RecordInResult --創建存儲過程user_RecordInResult
@charWhere nvarchar(1000) --定義變量

AS
declare @sql varchar(8000)
Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int
declare @sInvCode varchar(50),@eInvCode varchar(50)

if object_id('tempdb..resultTemp') is not null --判斷tempdb庫表名是否存在resultTemp
drop table tempdb..resultTemp --存在時刪除

if LTRIM(RTRIM(@charwhere))<>''
begin 
    SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10--取字符串@CHARWHERE存在"INVCODE>="的開始位置，不存在等於0,在位置上加10有誤，應該是加9就行了(INVCODE<=)長度為9
    SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10--取字符串@CHARWHERE存在"INVCODE<="的開始位置，不存在等於0,在位置上加10有誤，應該是加9就行了

    IF @B1>10 --成立時，說明字符串存在"INVCODE>="
        SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1)--取字符串在INVCODE>=字符后的內容
    IF @B2>10 --成立時，說明字符串存在"INVCODE<="
        SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2)--取字符串在INVCODE<=字符后的內容
end

select cInvName,cInvStd,cComUnitName ,
case when mon=1 then sum(isum) else 0 end '1月出',--后面的全部改為sum(case when mon=1 then isum else 0 end)
case when mon=2 then sum(isum) else 0 end '2月出',
case when mon=3 then sum(isum) else 0 end '3月出',
case when mon=4 then sum(isum) else 0 end '4月出',
case when mon=5 then sum(isum) else 0 end '5月出',
case when mon=6 then sum(isum) else 0 end '6月出',
case when mon=7 then sum(isum) else 0 end '7月出',
case when mon=8 then sum(isum) else 0 end '8月出',
case when mon=9 then sum(isum) else 0 end '9月出',
case when mon=10 then sum(isum) else 0 end '10月出',
case when mon=11 then sum(isum) else 0 end '11月出',
case when mon=12 then sum(isum) else 0 end '12月出'
into  tempdb..resultTemp --把結果生成tempdb庫的resultTemp表
FROM
(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon
from rdrecord r join rdrecords s on r.id=s.id
left join Inventory i on i.cInvcode=s.cInvCode
left join computationUnit u on u.cComUnitCode=i.cComUnitCode
where r.bRdFlag=0 

and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) 
and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0))

group by i.cInvName,i.cInvStd,u.cComUnitName,r.dDate ) a
group by cInvName,cInvStd,cComUnitName,mon

作者: roy_88 发布时间: 2011-11-28

SQL code

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='user_RecordInResult' AND TYPE='P') DROP PROC user_RecordInResult ---判断存储过程是否存在 存在就删除
GO
Create PROCEDURE user_RecordInResult
@charWhere nvarchar(1000)

AS  --- 创建 存储过程 定义变量
declare @sql varchar(8000)
Declare @A1 int,@A2 int,@B1 int,@B2 int,@C1 int,@C2 int
declare @sInvCode varchar(50),@eInvCode varchar(50)
if object_id('tempdb..resultTemp') is not null drop table tempdb..resultTemp

if LTRIM(RTRIM(@charwhere))<>''  --判断临时表是否存在 存在就删除
begin 
    SET @B1=CHARINDEX('INVCODE>=',@CHARWHERE)+10
    SET @B2=CHARINDEX('INVCODE<=',@CHARWHERE)+10

    IF @B1>10 SET @sInvCode=SUBSTRING(@CHARWHERE,@B1,CHARINDEX('''',@CHARWHERE,@B1)-@B1)
    IF @B2>10 SET @eInvCode=SUBSTRING(@CHARWHERE,@B2,CHARINDEX('''',@CHARWHERE,@B2)-@B2)
end

select cInvName,cInvStd,cComUnitName ,
case when mon=1 then sum(isum) else 0 end '1月出',--这里用sum(case when ...)应该比较好
case when mon=2 then sum(isum) else 0 end '2月出',
case when mon=3 then sum(isum) else 0 end '3月出',case when mon=4 then sum(isum) else 0 end '4月出',
case when mon=5 then sum(isum) else 0 end '5月出',case when mon=6 then sum(isum) else 0 end '6月出',
case when mon=7 then sum(isum) else 0 end '7月出',case when mon=8 then sum(isum) else 0 end '8月出',
case when mon=9 then sum(isum) else 0 end '9月出',case when mon=10 then sum(isum) else 0 end '10月出',
case when mon=11 then sum(isum) else 0 end '11月出',case when mon=12 then sum(isum) else 0 end '12月出'
into  tempdb..resultTemp ---生成临时表resultTemp
from(select i.cInvName,i.cInvStd,u.cComUnitName,sum(s.iQuantity) as isum,month(r.dDate) as mon
from rdrecord r join rdrecords s on r.id=s.id
left join Inventory i on i.cInvcode=s.cInvCode
left join computationUnit u on u.cComUnitCode=i.cComUnitCode
where r.bRdFlag=0 and (s.cInvCode>=@sInvCode or 0=ISNULL(@sInvCode,0)) and (s.cInvCode<=@eInvCode or 0=ISNULL(@eInvCode,0))
group by i.cInvName,i.cInvStd,u.cComUnitName,r.dDate ) a
group by cInvName,cInvStd,cComUnitName,mon