因为业务需要,公司需要查询所有产品从今天开始之前60天的库存情况和出入口详细信息,我使用的查询语句如下:
select t1.FNumber,convert(varchar,t1.FDate,104) as FDate,t2.iJCQuantity,t1.RKSL,t1.CKSL
from
(
SELECT
Rs.cInvCode as FNumber,
R.dDate as FDate ,
W.cWhCode as FcWhCode,
I.cInvCode as FcInvCode,
(CASE WHEN R.bRdFlag<>0 THEN '入库' ELSE '出库' END) AS SFBZ, Vt.cVouchName AS DJLX,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iQuantity ELSE '' END) AS RKSL,
(CASE WHEN R.bRdFlag<>0 THEN '' ELSE Rs.iQuantity END) AS CKSL,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iUnitCost ELSE '' END) AS RKDJ,
(CASE WHEN R.bRdFlag<>0 THEN convert(varchar(20),Rs.iPrice) ELSE '' END) AS RKJE FROM Customer C
RIGHT OUTER JOIN Department D
RIGHT OUTER JOIN SaleType
RIGHT OUTER JOIN Vendor V
RIGHT OUTER JOIN VouchType Vt
INNER JOIN Warehouse W
INNER JOIN Inventory I
INNER JOIN RdRecords Rs
INNER JOIN RdRecord R ON Rs.ID = R.ID
ON I.cInvCode = Rs.cInvCode
ON W.cWhCode = R.cWhCode
ON Vt.cVouchType = R.cVouchType
ON V.cVenCode = R.cVenCode
ON SaleType.cSTCode = R.cSTCode LEFT OUTER JOIN PurchaseType Pt
ON R.cPTCode = Pt.cPTCode LEFT OUTER JOIN Rd_Style Rd
ON R.cRdCode = Rd.cRdCode
ON D.cDepCode = R.cDepCode
ON C.cCusCode = R.cCusCode
AND ( R.[cWhCode]='0001'
OR R.[cWhCode]='0002'
OR R.[cWhCode]='0003'
OR R.[cWhCode]='0004'
OR R.[cWhCode]='0005'
OR R.[cWhCode]='0006'
OR R.[cWhCode]='0007'
OR R.[cWhCode]='0008'
OR R.[cWhCode]='0009'
OR R.[cWhCode]='9001'
OR R.[cWhCode]='9002' ))
t1,(
SELECT CS.cWhCode as FcWhCode, CS.cInvCode as FcInvCode , LTRIM(STR(CS.iQuantity,20,2)) AS iJCQuantity FROM (Warehouse AS W INNER JOIN CurrentStock AS CS ON W.cWhCode = CS.cWhCode) INNER JOIN Inventory AS I ON CS.cInvCode = I.cInvCode
WHERE (1=1) AND W.cWhCode = '0001' or W.cWhCode='0003'
) t2
where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=60
order by convert(varchar,t1.FDate,121)其中某一产品的显示结果为
220820205 31.05.2010 93.00 98.0 0.0
其中“220820205 ”为产品编码,“31.05.2010”为日期,“93.00”为库存数量,“98.0”为入库数量,“0.0”为出库数量,可现在公司要求产品编码应该显示为“220 820 205”,每三个数字一个空格,好像是德国那边的数字格式,还有要求入库和出库数量如果为零的话必须使用“空”代替,个人感觉这两个要求的实现需要在前面第一行定义的时候加上参数,就跟日期那个一样,可就是不知道怎么加参数,加什么参数。
本人对SQL基本上是一窍不通,以上代码还是朋友给弄的,现在朋友出差联系不上,希望各位高手能指点一二,在此先谢过了!
select t1.FNumber,convert(varchar,t1.FDate,104) as FDate,t2.iJCQuantity,t1.RKSL,t1.CKSL
from
(
SELECT
Rs.cInvCode as FNumber,
R.dDate as FDate ,
W.cWhCode as FcWhCode,
I.cInvCode as FcInvCode,
(CASE WHEN R.bRdFlag<>0 THEN '入库' ELSE '出库' END) AS SFBZ, Vt.cVouchName AS DJLX,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iQuantity ELSE '' END) AS RKSL,
(CASE WHEN R.bRdFlag<>0 THEN '' ELSE Rs.iQuantity END) AS CKSL,
(CASE WHEN R.bRdFlag<>0 THEN Rs.iUnitCost ELSE '' END) AS RKDJ,
(CASE WHEN R.bRdFlag<>0 THEN convert(varchar(20),Rs.iPrice) ELSE '' END) AS RKJE FROM Customer C
RIGHT OUTER JOIN Department D
RIGHT OUTER JOIN SaleType
RIGHT OUTER JOIN Vendor V
RIGHT OUTER JOIN VouchType Vt
INNER JOIN Warehouse W
INNER JOIN Inventory I
INNER JOIN RdRecords Rs
INNER JOIN RdRecord R ON Rs.ID = R.ID
ON I.cInvCode = Rs.cInvCode
ON W.cWhCode = R.cWhCode
ON Vt.cVouchType = R.cVouchType
ON V.cVenCode = R.cVenCode
ON SaleType.cSTCode = R.cSTCode LEFT OUTER JOIN PurchaseType Pt
ON R.cPTCode = Pt.cPTCode LEFT OUTER JOIN Rd_Style Rd
ON R.cRdCode = Rd.cRdCode
ON D.cDepCode = R.cDepCode
ON C.cCusCode = R.cCusCode
AND ( R.[cWhCode]='0001'
OR R.[cWhCode]='0002'
OR R.[cWhCode]='0003'
OR R.[cWhCode]='0004'
OR R.[cWhCode]='0005'
OR R.[cWhCode]='0006'
OR R.[cWhCode]='0007'
OR R.[cWhCode]='0008'
OR R.[cWhCode]='0009'
OR R.[cWhCode]='9001'
OR R.[cWhCode]='9002' ))
t1,(
SELECT CS.cWhCode as FcWhCode, CS.cInvCode as FcInvCode , LTRIM(STR(CS.iQuantity,20,2)) AS iJCQuantity FROM (Warehouse AS W INNER JOIN CurrentStock AS CS ON W.cWhCode = CS.cWhCode) INNER JOIN Inventory AS I ON CS.cInvCode = I.cInvCode
WHERE (1=1) AND W.cWhCode = '0001' or W.cWhCode='0003'
) t2
where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=60
order by convert(varchar,t1.FDate,121)其中某一产品的显示结果为
220820205 31.05.2010 93.00 98.0 0.0
其中“220820205 ”为产品编码,“31.05.2010”为日期,“93.00”为库存数量,“98.0”为入库数量,“0.0”为出库数量,可现在公司要求产品编码应该显示为“220 820 205”,每三个数字一个空格,好像是德国那边的数字格式,还有要求入库和出库数量如果为零的话必须使用“空”代替,个人感觉这两个要求的实现需要在前面第一行定义的时候加上参数,就跟日期那个一样,可就是不知道怎么加参数,加什么参数。
本人对SQL基本上是一窍不通,以上代码还是朋友给弄的,现在朋友出差联系不上,希望各位高手能指点一二,在此先谢过了!
case t1.RKSL when 0 then NULL else t1.RKSL END RKSL,
case t1.CKSL when 0 then NULL else t1.CKSL END CKSL
from
select t1.FNumber,convert(varchar,t1.FDate,104) as FDate,t2.iJCQuantity,
case t1.RKSL when 0 then '' else convert(varchar,t1.RKSL) END RKSL,
case t1.CKSL when 0 then '' else (varchar,t1.CKSL) END CKSL
from
FNu mbe 789 31.05.2010 249.00 99.0 0.0
FNu mbe 789 31.05.2010 93.00 98.0 0.0
FNu mbe 789 31.05.2010 1.00 95.0 0.0
FNu mbe 789 31.05.2010 872.00 100.0 0.0
FNu mbe 789 31.05.2010 617.00 100.0 0.0
FNu mbe 789 31.05.2010 0.00 59.0 0.0??
显示为"123 456 789"后边的a没有了,但又体现不出特殊的型号,因此觉定直接将有字母的特殊型号过滤掉,不让其显示了,不知道可不可以实现?谢谢
select left('123456789a',3)+' '+substring('123456789a',4,3)+' '+substring('123456789a',7,len('123456789a')-6)
--或者
select left('123456789a',3)+' '+substring('123456789a',4,3)+' '+ Right('123456789a',len('123456789a')-6)
220 820 201 02.06.2010 109.00 100
320 511 035b 02.06.2010 691.00 100
320 811 048b 02.06.2010 249.00 96
320 820 048b 02.06.2010 100
320 520 244b 02.06.2010 47
320 820 048b 02.06.2010 100
320 511 035b 02.06.2010 691.00 96
320 511 035b 02.06.2010 691.00 98
320 820 003b 02.06.2010 199.00 100 想请教一下,能不能使用判断句先判断一下我的“FNumber”这一列的内容是不是数字,只取是数字的,或者判断一下有多少位字符,只要等于9位的内容,不知道该如何写代码呢?没有接触过,很多初级问题都不懂,谢谢大家了
or
where ISNUMERIC(FNumber) = i --判断是数字
patindex('%[^0-9]%',FNumber) = 0
或者patindex('%[^0-9]%',FNumber) = 0加到所有代码最后面的where语句where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=60
order by convert(varchar,t1.FDate,121)再在判断数字或字符位数的语句前加上and就OK了,如下
where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=60
and patindex('%[^0-9]%',FNumber) = 0
order by convert(varchar,t1.FDate,121)
查询的结果也全部正确了
220 820 058 03.06.2010 35.00 NULL 2
220 820 027 03.06.2010 29.00 NULL 10
320 820 108 03.06.2010 1895.00 100 NULL
320 820 108 03.06.2010 1895.00 100 NULL
320 820 108 03.06.2010 1895.00 100 NULL
320 820 108 03.06.2010 1895.00 94 NULL
620 100 350 03.06.2010 86.00 NULL 70
320 820 023 03.06.2010 874.00 NULL 1
320 820 024 03.06.2010 95.00 NULL 10
320 820 011 03.06.2010 200.00 NULL 100
320 820 123 03.06.2010 476.00 NULL 20不过加上这个条件之后根绝查询速度变慢了,谢谢“hokor”和“SQL77”的热心帮助!