如何根据日期提取每天的结存数?代码:SELECT DISTINCT R.ID,Rs.AutoID,VT.cVouchType, VT.cVouchName, R.cWhCode, Rs.cInvCode, dDate,dVeriDate, R.bRdFlag AS YL,
R.cBusType,R.cBusCode, R.cCode, DP.cDepName, P1.cPersonName, R.cHandler, R.cMaker, Rs.cBatch,
CONVERT(VARCHAR(10), Rs.dVDate, 102) as dVDate, CASE WHEN I.iGroupType =0 THEN NULL WHEN I.iGroupType = 2 THEN
(CASE WHEN Rs.iQuantity = 0.0 OR Rs.iNum = 0.0 THEN NULL ELSE Rs.iQuantity/Rs.iNum END) WHEN I.iGroupType = 1 THEN CU_G.iChangRate END AS iChangRate,
CASE WHEN R.bRdFlag = 1 THEN (CASE WHEN I.iGroupType = 0 THEN 0 WHEN I.iGroupType = 2 THEN IsNull(Rs.iNum,0) WHEN I.iGRoupType = 1
THEN Rs.iQuantity/CU_G.iChangRate END) ELSE NULL END AS iInNum, CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iInQtty,
CASE WHEN R.bRdFlag = 0 THEN (CASE WHEN I.iGroupType = 0 THEN 0 WHEN I.iGroupType = 2 THEN IsNull(Rs.iNum,0) WHEN I.iGRoupType = 1
THEN Rs.iQuantity/CU_G.iChangRate END) ELSE NULL END AS iOutNum, CASE WHEN R.bRdFlag = 0 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iOutQtty,
C.cCusAbbName, V.cVenAbbName, VT.cVouchName, Rd.cRdName, W.cWhName, R.cMemo, R.cDefine1,R.cDefine2, R.cDefine3, R.cDefine4, R.cDefine5, R.cDefine6,
R.cDefine7, R.cDefine8, R.cDefine9, R.cDefine10, R.cDefine11, R.cDefine12, R.cDefine13, R.cDefine14, R.cDefine15, R.cDefine16,Rs.cDefine22, Rs.cDefine23,
Rs.cDefine24, Rs.cDefine25, Rs.cDefine26, Rs.cDefine27,Rs.cDefine28, Rs.cDefine29, Rs.cDefine30, Rs.cDefine31, Rs.cDefine32, Rs.cDefine33, Rs.cDefine34,
Rs.cDefine35, Rs.cDefine36, Rs.cDefine37, Rs.cFree1, Rs.cFree2, Rs.cFree3, Rs.cFree4, Rs.cFree5, Rs.cFree6 , Rs.cFree7, Rs.cFree8, Rs.cFree9, Rs.cFree10,
Rs.dCheckDate , Rs.cCheckCode, CheckPerson.cPersonName as cCheckPersonName, Rs.cRejectCode FROM dbo.Inventory I
LEFT OUTER JOIN dbo.ComputationUnit CU_G ON I.cSTComUnitCode =CU_G.cComUnitCode
RIGHT OUTER JOIN dbo.Vendor V RIGHT OUTER JOIN dbo.Customer C RIGHT OUTER JOIN dbo.RdRecord R LEFT OUTER JOIN dbo.Person P1
ON R.cPersonCode = P1.cPersonCode LEFT OUTER JOIN dbo.Department DP ON R.cDepCode = DP.cDepCode LEFT OUTER JOIN dbo.Warehouse W
ON R.cWhCode = W.cWhCode LEFT OUTER JOIN dbo.RdRecords Rs ON R.ID = Rs.ID LEFT OUTER JOIN dbo.VouchType VT ON R.cVouchType = VT.cVouchType
LEFT OUTER JOIN dbo.Rd_Style Rd ON R.cRdCode = Rd.cRdCode ON C.cCusCode = R.cCusCode ON V.cVenCode = R.cVenCode ON I.cInvCode = Rs.cInvCode
left outer join Person CheckPerson on CheckPerson.cPersonCode = Rs.cCheckPersonCode WHERE ((R.dDate < '2009-11-01' And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2009-11-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) AND IsNull(R.bIsStQc,0) = 0 AND R.dDate >= '2009-11-01' AND Rs.cInvCode = '0101001' ORDER BY dDate,R.ID我要实现如何提取这行代码里边的数量字段(iQuantity)结存数:CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iInQtty,
R.cBusType,R.cBusCode, R.cCode, DP.cDepName, P1.cPersonName, R.cHandler, R.cMaker, Rs.cBatch,
CONVERT(VARCHAR(10), Rs.dVDate, 102) as dVDate, CASE WHEN I.iGroupType =0 THEN NULL WHEN I.iGroupType = 2 THEN
(CASE WHEN Rs.iQuantity = 0.0 OR Rs.iNum = 0.0 THEN NULL ELSE Rs.iQuantity/Rs.iNum END) WHEN I.iGroupType = 1 THEN CU_G.iChangRate END AS iChangRate,
CASE WHEN R.bRdFlag = 1 THEN (CASE WHEN I.iGroupType = 0 THEN 0 WHEN I.iGroupType = 2 THEN IsNull(Rs.iNum,0) WHEN I.iGRoupType = 1
THEN Rs.iQuantity/CU_G.iChangRate END) ELSE NULL END AS iInNum, CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iInQtty,
CASE WHEN R.bRdFlag = 0 THEN (CASE WHEN I.iGroupType = 0 THEN 0 WHEN I.iGroupType = 2 THEN IsNull(Rs.iNum,0) WHEN I.iGRoupType = 1
THEN Rs.iQuantity/CU_G.iChangRate END) ELSE NULL END AS iOutNum, CASE WHEN R.bRdFlag = 0 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iOutQtty,
C.cCusAbbName, V.cVenAbbName, VT.cVouchName, Rd.cRdName, W.cWhName, R.cMemo, R.cDefine1,R.cDefine2, R.cDefine3, R.cDefine4, R.cDefine5, R.cDefine6,
R.cDefine7, R.cDefine8, R.cDefine9, R.cDefine10, R.cDefine11, R.cDefine12, R.cDefine13, R.cDefine14, R.cDefine15, R.cDefine16,Rs.cDefine22, Rs.cDefine23,
Rs.cDefine24, Rs.cDefine25, Rs.cDefine26, Rs.cDefine27,Rs.cDefine28, Rs.cDefine29, Rs.cDefine30, Rs.cDefine31, Rs.cDefine32, Rs.cDefine33, Rs.cDefine34,
Rs.cDefine35, Rs.cDefine36, Rs.cDefine37, Rs.cFree1, Rs.cFree2, Rs.cFree3, Rs.cFree4, Rs.cFree5, Rs.cFree6 , Rs.cFree7, Rs.cFree8, Rs.cFree9, Rs.cFree10,
Rs.dCheckDate , Rs.cCheckCode, CheckPerson.cPersonName as cCheckPersonName, Rs.cRejectCode FROM dbo.Inventory I
LEFT OUTER JOIN dbo.ComputationUnit CU_G ON I.cSTComUnitCode =CU_G.cComUnitCode
RIGHT OUTER JOIN dbo.Vendor V RIGHT OUTER JOIN dbo.Customer C RIGHT OUTER JOIN dbo.RdRecord R LEFT OUTER JOIN dbo.Person P1
ON R.cPersonCode = P1.cPersonCode LEFT OUTER JOIN dbo.Department DP ON R.cDepCode = DP.cDepCode LEFT OUTER JOIN dbo.Warehouse W
ON R.cWhCode = W.cWhCode LEFT OUTER JOIN dbo.RdRecords Rs ON R.ID = Rs.ID LEFT OUTER JOIN dbo.VouchType VT ON R.cVouchType = VT.cVouchType
LEFT OUTER JOIN dbo.Rd_Style Rd ON R.cRdCode = Rd.cRdCode ON C.cCusCode = R.cCusCode ON V.cVenCode = R.cVenCode ON I.cInvCode = Rs.cInvCode
left outer join Person CheckPerson on CheckPerson.cPersonCode = Rs.cCheckPersonCode WHERE ((R.dDate < '2009-11-01' And IsNull(R.bIsSTQc,0) = 1)
Or (R.dDate >= '2009-11-01'
And IsNull(R.bPUFirst,0) = 0
And IsNull(R.bIAFirst,0) = 0
And (not (R.cBusType = '假退料'and R.cVouchType = 11))
)
) AND IsNull(R.bIsStQc,0) = 0 AND R.dDate >= '2009-11-01' AND Rs.cInvCode = '0101001' ORDER BY dDate,R.ID我要实现如何提取这行代码里边的数量字段(iQuantity)结存数:CASE WHEN R.bRdFlag = 1 THEN ISNULL(Rs.iQuantity,0) ELSE NULL END AS iInQtty,
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
我有数m=10,想对表中的某个字段数进行减少,直到我提供的数为0
在表pp(id,num,week)
假设表有数据
1 3 2
2 2 2
3 1 3
4 1 5
5 8 3 我希望执行一个语句,就是字段num减少,到0为至,如果是表格中数据的最后一行允许为负。例如m=10,执行后的结果表是:
1 0 2
2 0 2
3 0 3
4 0 5
5 5 3 如果m=20,最前面的数据执行语句后表格是:
1 0 2
2 0 2
3 0 3
4 0 5
5 -5 3 请高手指点,谢谢!!
create table pp(id int,num int,week int)
insert into pp values(1, 3, 2 )
insert into pp values(2, 2, 2 )
insert into pp values(3, 1, 3 )
insert into pp values(4, 1, 5 )
insert into pp values(5, 8, 3 )
go
declare @m as intset @m = 10
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 and id <> (select max(id) from pp) then 0 else (select sum(num) from pp where id <= t.id) - @m end , week from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 5 3(所影响的行数为 5 行)
*/set @m = 20
select id , num = case when (select sum(num) from pp where id <= t.id) - @m <= 0 and id <> (select max(id) from pp) then 0 else (select sum(num) from pp where id <= t.id) - @m end , week from pp t
/*
id num week
----------- ----------- -----------
1 0 2
2 0 2
3 0 3
4 0 5
5 -5 3(所影响的行数为 5 行)
*/drop table pp