我运行了查询语句,结果如下(以320 820 611为例),下边5列分别表示的是产品编码、日期、库存数、入库、出库。
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 NULL 200但我想得到下边的结果
320 820 611 13.06.2010 473.00 NULL NULL
320 820 611 13.06.2010 NULL 300 NULL
320 820 611 13.06.2010 NULL NULL 200 详细说明:
要求同一天的同一款产品(编码相同)
1.如果库存、入库和出库都有数据那就要有三行,第一行只有第三列(库存数)有数字,其余两列值为空,第二行只有第四列有数据,该数据为全天入库的总计,其余列为空,但三行只有出库的那一列即最后一列有数据,该数据为全天出库数量之和,其余列为空。
2.如果只有库存和入库的数据那就只要有两行,第一行只有第三列(库存数)有数字,其余两列值为空,第二行只有第四列有数据,该数据为全天入库的总计,其余列为空,不需要第三列。
3.如果只有库存和出库的数量同上,只需要两行就可以。
查询语句如下
select stuff(stuff(t1.FNumber,4,0,' '),8,0,' ' ) as FNumber ,
convert(varchar,t1.FDate,104) as FDate,
(case when ltrim(str(t2.iJCQuantity))='0' then null else cast(t2.iJCQuantity as varchar(20)) end) as iJCQuantity,
(case when ltrim(str(t1.RKSL))='0' then null else cast(t1.RKSL as varchar(20)) end) as RKSL,
(case when ltrim(str(t1.CKSL))='0' then null else cast(t1.CKSL as varchar(20)) end) as 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,
--nvert(varchar(10),Rs.dVDate,102) AS SXRQ,
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
--LTRIM(STR(CS.iQuantity,20,2)) as FQty
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
WHERE R.cVouchType <> '33'
AND R.cVouchType <> '34'
AND R.cVouchType IN ( '','01','32','08','09','10','')
AND
(1=1) AND ( ( R.[dDate] >= '2010-01-01') )
--AND Rs.cInvCode >= '222020844b'
--AND Rs.cInvCode <= '222020844b'
AND (1=1) AND (1=1) AND (1=1)
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' )
--ORDER BY R.dDate
)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' --Order by CS.cWhCode , CS.cInvCode
) t2where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=59
and len(FNumber) = 9
order by convert(varchar,t1.FDate,121)
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 100 NULL
320 820 611 13.06.2010 473.00 NULL 200但我想得到下边的结果
320 820 611 13.06.2010 473.00 NULL NULL
320 820 611 13.06.2010 NULL 300 NULL
320 820 611 13.06.2010 NULL NULL 200 详细说明:
要求同一天的同一款产品(编码相同)
1.如果库存、入库和出库都有数据那就要有三行,第一行只有第三列(库存数)有数字,其余两列值为空,第二行只有第四列有数据,该数据为全天入库的总计,其余列为空,但三行只有出库的那一列即最后一列有数据,该数据为全天出库数量之和,其余列为空。
2.如果只有库存和入库的数据那就只要有两行,第一行只有第三列(库存数)有数字,其余两列值为空,第二行只有第四列有数据,该数据为全天入库的总计,其余列为空,不需要第三列。
3.如果只有库存和出库的数量同上,只需要两行就可以。
查询语句如下
select stuff(stuff(t1.FNumber,4,0,' '),8,0,' ' ) as FNumber ,
convert(varchar,t1.FDate,104) as FDate,
(case when ltrim(str(t2.iJCQuantity))='0' then null else cast(t2.iJCQuantity as varchar(20)) end) as iJCQuantity,
(case when ltrim(str(t1.RKSL))='0' then null else cast(t1.RKSL as varchar(20)) end) as RKSL,
(case when ltrim(str(t1.CKSL))='0' then null else cast(t1.CKSL as varchar(20)) end) as 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,
--nvert(varchar(10),Rs.dVDate,102) AS SXRQ,
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
--LTRIM(STR(CS.iQuantity,20,2)) as FQty
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
WHERE R.cVouchType <> '33'
AND R.cVouchType <> '34'
AND R.cVouchType IN ( '','01','32','08','09','10','')
AND
(1=1) AND ( ( R.[dDate] >= '2010-01-01') )
--AND Rs.cInvCode >= '222020844b'
--AND Rs.cInvCode <= '222020844b'
AND (1=1) AND (1=1) AND (1=1)
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' )
--ORDER BY R.dDate
)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' --Order by CS.cWhCode , CS.cInvCode
) t2where t1.FcWhCode=t2.FcWhCode and t1.FCInvCode=t2.FCInvCode
and datediff(day,t1.FDate,getdate())<=59
and len(FNumber) = 9
order by convert(varchar,t1.FDate,121)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货