select b.*,
case when (select sum(thiscount) from @t a where a.tno=b.tno and a.ordno<=b.ordno)<=isnull(c.tnoCountAll,0)
then 'OK'
else 'NG'
end as re
from @t b
left join @tA c
on b.tno=c.tno
case when (select sum(thiscount) from @t a where a.tno=b.tno and a.ordno<=b.ordno)<=isnull(c.tnoCountAll,0)
then 'OK'
else 'NG'
end as re
from @t b
left join @tA c
on b.tno=c.tno
解决方案 »
- sql server2000怎么查看远程连接ip
- 查询数据的计算
- 如何给SQL 的 select 语句添加一个自动递增的ID字段
- 关于数据库表的设计问题
- 我是建一张表,表中有一列是email 输入:要求必须要有“@”,请问怎么弄???
- 在线求教各位高手!急!!!
- 有请高手解决sql server2005的问题~~~~~~~~~~~~~~~
- 我这个问题很麻烦,关于删除数据表中已存在某列的问题!
- [求教] 请大家谈谈INNER JOIN, LEFT JOIN OR ROGHT JOIN OR CROSS JOIN好么?(0分)
- 大声喊叫:有没有在江苏软件园的朋友啊???????????
- sql2005 window验证可以连上,sql server 不能连接。并报错???
- 如何编写这样的触发器(金蝶系统)
declare @t table(ordno varchar(2),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '01', 'H01001','24'
union all select '02','H01003','46'
union all select '03','H01001','10'
union all select '04','H01002','5'
union all select '05','H01003','95'
union all select '06','H01006','86'
declare @tA table(tno varchar(10),tnoCountAll [numeric](18, 2))
insert @tA select 'H01001','30'
union all select 'H01002','400'
union all select 'H01003','10'
union all select 'H01006','186' select b.*,
case when (select sum(thiscount) from @t a where a.tno=b.tno and a.ordno<=b.ordno)<=isnull(c.tnoCountAll,0)
then 'OK'
else 'NG'
end as re
from @t b
left join @tA c
on b.tno=c.tno/*
01 H01001 24.00 OK
02 H01003 46.00 NG
03 H01001 10.00 NG
04 H01002 5.00 OK
05 H01003 95.00 NG
06 H01006 86.00 OK*/
/*
ordno tno thisCount re
----- ---------- -------------------- ----
01 H01001 24.00 OK
02 H01003 46.00 NG
03 H01001 10.00 NG
04 H01002 5.00 OK
05 H01003 95.00 NG
06 H01006 86.00 OK(所影响的行数为 6 行)
*/
但是不好意思,剛我看了實際的流程是兩個排序,此方法就會有些狀況
--用量表
--ordno:排序1,xtno:排序2,tno:编号,thisCount,此编号此次需要用量
declare @t table(ordno varchar(2),xtno varchar(12),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '1','X003','H01001','24'
union all select '1','X003','H01003','46'
union all select '2','X001','H01001','10'
union all select '20','X004','H01002','5'
union all select '20','X005','H01003','95'
union all select '20','X006','H01006','86'
union all select '20','X006','H01002','46'
select * from @t order by ordno,xtno--库存表
--tno:编号,tnoCountAll 此编号总库存量
declare @tA table(tno varchar(10),tnoCountAll [numeric](18, 2))
insert @tA select 'H01001','30'
union all select 'H01002','400'
union all select 'H01003','10'
union all select 'H01006','186'
select * from @tA
select * from @t order by ordno,xtno
按第一個用量表,從上至下匹對下來
--xtno:排序2?好象没什么用处.
select a.* , re = case when 0 <= (b.tnocountall - (select sum(thiscount) from @t where tno = a.tno and ordno <= a.ordno)) then 'OK' else 'NG' end from @t a, @ta b where a.tno = b.tno order by a.ordno
/*
ordno xtno tno thisCount re
----- ------------ ---------- -------------------- ----
1 X003 H01001 24.00 OK
1 X003 H01003 46.00 NG
2 X001 H01001 10.00 NG
20 X004 H01002 5.00 OK
20 X005 H01003 95.00 NG
20 X006 H01006 86.00 OK
20 X006 H01002 46.00 OK(所影响的行数为 7 行)
*/
按这个的话,这四条数据两两一样?
所以我觉得xtno没用,应该是按tno,ordno排序就行了.ordno xtno tno thisCount
----- ------------ ---------- --------------------
1 X003 H01001 24.00
1 X003 H01003 46.0020 X006 H01006 86.00
20 X006 H01002 46.00
--ordno:排序1,xtno:排序2,tno:编号,thisCount,此编号此次需要用量
declare @t table(ordno varchar(2),xtno varchar(12),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '1','X003','H01001','24'
union all select '1','X003','H01003','46'
union all select '2','X001','H01001','10'
union all select '20','X004','H01002','5'
union all select '20','X005','H01003','95'
union all select '20','X006','H01006','86'
union all select '20','X006','H01002','46'
select * from @t order by ordno,xtno--库存表
--tno:编号,tnoCountAll 此编号总库存量
declare @tA table(tno varchar(10),tnoCountAll [numeric](18, 2))
insert @tA select 'H01001','30'
union all select 'H01002','10'
union all select 'H01003','10'
union all select 'H01006','186'
select * from @tA --如果為此答案的話,請看一下tno=H01002的結果,都為HG了,其實第一個應該為OK
select b.*,
case when (select sum(thiscount) from @t a where a.tno=b.tno and a.ordno<=b.ordno)<=isnull(c.tnoCountAll,0)
then 'OK'
else 'NG'
end as re
from @t b
left join @tA c
on b.tno=c.tno
select a.*
, re = case when (select sum(thiscount) from @t where tno = a.tno
and right('00'+ordno,2)+xtno <= right('00'+a.ordno,2)+a.xtno)<=b.tnocountall
then 'OK' else 'NG' end
from @t a, @ta b where a.tno = b.tno order by a.ordno
select id=identity(int,1,1),*
into #t
from @t order by ordno,xtno對#t用剛剛的語句(id 代替ordno)
結案
--ordno:排序1,xtno:排序2,tno:编号,thisCount,此编号此次需要用量
declare @t table(ordno varchar(2),xtno varchar(12),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '1','X003','H01001','24'
union all select '1','X003','H01003','46'
union all select '2','X001','H01001','10'
union all select '20','X004','H01002','5'
union all select '20','X005','H01003','95'
union all select '20','X006','H01006','86'
union all select '20','X006','H01002','46'
--select * from @t order by ordno,xtno--库存表
--tno:编号,tnoCountAll 此编号总库存量
declare @tA table(tno varchar(10),tnoCountAll [numeric](18, 2))
insert @tA select 'H01001','30'
union all select 'H01002','10'
union all select 'H01003','10'
union all select 'H01006','186'
--select * from @tA --添加臨時表
select id=identity(int,1,1),*
into #t
from @t order by ordno,xtno select b.*,
case when (select sum(thiscount) from #t a where a.tno=b.tno and a.id<=b.id)<=isnull(c.tnoCountAll,0)
then 'OK'
else 'NG'
end as re
from #t b
left join @tA c
on b.tno=c.tno
/*
1 1 X003 H01001 24.00 OK
2 1 X003 H01003 46.00 NG
3 2 X001 H01001 10.00 NG
4 20 X004 H01002 5.00 OK
5 20 X005 H01003 95.00 NG
6 20 X006 H01006 86.00 OK
7 20 X006 H01002 46.00 NG*/drop table #t
--ordno:排序1,xtno:排序2,tno:编号,thisCount,此编号此次需要用量
declare @t table(ordno varchar(2),xtno varchar(12),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '1','X003','H01001','24'
union all select '1','X003','H01003','46'
union all select '2','X001','H01001','10'
union all select '20','X004','H01002','5'
union all select '20','X005','H01003','95'
union all select '20','X006','H01006','86'
union all select '20','X006','H01002','46'
--select * from @t order by ordno,xtno--库存表
--tno:编号,tnoCountAll 此编号总库存量
declare @tA table(tno varchar(10),tnoCountAll [numeric](18, 2))
insert @tA select 'H01001','30'
union all select 'H01002','10'
union all select 'H01003','10'
union all select 'H01006','186'
--select * from @tA
select a.*,case when
(select sum(thisCount) from @t where a.ordno >=ordno and a.tno = tno)<=b.tnocountall
then 'OK' else 'NG' END AS result
from @t a,@ta b where a.tno = b.tno
/*
ordno xtno tno thisCount result
----- ------------ ---------- -------------------- ------
1 X003 H01001 24.00 OK
1 X003 H01003 46.00 NG
2 X001 H01001 10.00 NG
20 X004 H01002 5.00 NG
20 X005 H01003 95.00 NG
20 X006 H01006 86.00 OK
20 X006 H01002 46.00 NG
*/
不得不說,其實這樣是有問題的...哎
用這個測測看declare @t table(ordno varchar(2),xtno varchar(12),tno varchar(10),thisCount [numeric](18, 2))
insert @t select '1','X003','H01001','24'
union all select '1','X003','H01003','46'
union all select '2','X001','H01001','10'
union all select '10','X004','H01002','5'
union all select '20','X005','H01003','95'
union all select '20','X006','H01006','86'
union all select '2','X006','H01002','46'
and right('00'+ordno,2)+xtno <= right('00'+a.ordno,2)+a.xtno前面加0再比較就沒有問題