34,590
社区成员
发帖
与我相关
我的任务
分享请教SQL问题
有一组数据
FINISH_ QTY RAC_ID DOC_ID
100 010 72a4d16d8631
90 020 72a4d16d8631
80 030 72a4d16d8631
70 040 72a4d16d8631
60 041 72a4d16d8631
60 050 72a4d16d8631
0 060 72a4d16d8631
0 070 72a4d16d8631
0 080 72a4d16d8631
RAC_ID 是工序号，DOC_ID是产品ID ，FINISH_ QTY是每道工序的完工量，
每道工序的可用量也就是前道工序的完工量，请问怎么查询出每道工序的可用量？
工序080的前面一道不一定是070 有可能是071 反正是比这个工序小的最大那个。
FINISH_ QTY RAC_ID DOC_ID
100 010 72a4d16d8631
90 020 72a4d16d8631
80 030 72a4d16d8631
70 040 72a4d16d8631
60 041 72a4d16d8631
60 050 72a4d16d8631
0 060 72a4d16d8631
0 070 72a4d16d8631
0 080 72a4d16d8631
RAC_ID 是工序号，DOC_ID是产品ID ，FINISH_ QTY是每道工序的完工量，
每道工序的可用量也就是前道工序的完工量，请问怎么查询出每道工序的可用量？
工序080的前面一道不一定是070 有可能是071 反正是比这个工序小的最大那个。
...全文
请发表友善的回复…
发表回复
shoppo0505 2016-03-21
- 打赏
- 举报
你好你这个我试了下能把这些测试数据查出来 ，我现在表里面几万条数据的话 这个语句该怎么改？[/quote]
前面那部分去掉就行。
;with temp as
(
select
_Index = ROW_NUMBER()over(order by RAC_ID)
, * from 你的表格名称
)
select a.*, can_be_used=ISNULL(b.FINISH_QTY, '') from temp a
left outer join temp b on a._Index-1 = b._Index
中国风 2016-03-19
- 打赏
- 举报
SELECT a.*,ISNULL(b.[FINISH_ QTY],0) AS [FINISH_ QTY]
FROM B AS a
LEFT JOIN B AS b ON a.[DOC_ID] = b.[DOC_ID]
AND b.[RAC_ID] = ( SELECT MAX([RAC_ID])
FROM B
WHERE [DOC_ID] = b.[DOC_ID]
AND [RAC_ID] < a.[RAC_ID]
);
zhenshz 2016-03-19
- 打赏
- 举报
select c.*,isnull(m.[FINISH_ QTY],'') aa from
(select row_number()over(order by RAC_ID) pm,* from b) as c
left join
(select row_number()over(order by RAC_ID) pm,* from b) m
on c.pm-1=m.pm
唐诗三百首 2016-03-19
- 打赏
- 举报
CREATE TABLE [dbo].[B](
[FINISH_QTY] [varchar](50),
[RAC_ID] [varchar](50) ,
[DOC_ID] [varchar](50)
)
insert into B values('100','010','72a4d16d8631')
insert into B values('90','020','72a4d16d8631')
insert into B values('80','030','72a4d16d8631')
insert into B values('70','040','72a4d16d8631')
insert into B values('60','041','72a4d16d8631')
insert into B values('0','060','72a4d16d8631')
insert into B values('0','070','72a4d16d8631')
insert into B values('0','080','72a4d16d8631')
with t as
(select *,row_number() over(partition by DOC_ID order by RAC_ID) 'rn'
from [B])
select a.FINISH_QTY,
a.RAC_ID,
a.DOC_ID,
'可用量'=isnull(b.FINISH_QTY,'0')
from t a
left join t b on a.DOC_ID=b.DOC_ID
and a.rn=b.rn+1
/*
FINISH_QTY RAC_ID DOC_ID 可用量
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
100 010 72a4d16d8631 0
90 020 72a4d16d8631 100
80 030 72a4d16d8631 90
70 040 72a4d16d8631 80
60 041 72a4d16d8631 70
0 060 72a4d16d8631 60
0 070 72a4d16d8631 0
0 080 72a4d16d8631 0
(8 row(s) affected)
*/
laotiesh 2016-03-19
- 打赏
- 举报
你好你这个我试了下能把这些测试数据查出来 ，我现在表里面几万条数据的话 这个语句该怎么改？
shoppo0505 2016-03-18
- 打赏
- 举报
with data (FINISH_QTY, RAC_ID, DOC_ID) as
(
select 100, '010', '72a4d16d8631' union all
select 90, '020', '72a4d16d8631' union all
select 80, '030', '72a4d16d8631' union all
select 70, '040', '72a4d16d8631' union all
select 60, '041', '72a4d16d8631' union all
select 0, '060', '72a4d16d8631' union all
select 0, '070', '72a4d16d8631' union all
select 60, '050', '72a4d16d8631' union all
select 0, '080', '72a4d16d8631'
)
, temp as
(
select
_Index = ROW_NUMBER()over(order by RAC_ID)
, data.* from data
)
select a.*, can_be_used=ISNULL(b.FINISH_QTY, '') from temp a
left outer join temp b on a._Index-1 = b._Index
laotiesh 2016-03-18
- 打赏
- 举报
以下是测试数据
CREATE TABLE [dbo].[B](
[FINISH_ QTY] [varchar](50),
[RAC_ID] [varchar](50) ,
[DOC_ID] [varchar](50)
)
insert into B values('100','010','72a4d16d8631')
insert into B values('90','020','72a4d16d8631')
insert into B values('80','030','72a4d16d8631')
insert into B values('70','040','72a4d16d8631')
insert into B values('60','041','72a4d16d8631')
insert into B values('0','060','72a4d16d8631')
insert into B values('0','070','72a4d16d8631')
insert into B values('0','080','72a4d16d8631')
go
