高手帮忙写个sql

hqs19821108 2010-03-18 02:31:02

ＭＦ_TZ

tz_no(通知单号) MO_NO（制令单号） QTY（数量）
TZ001 MO001 3000
TZ002 MO001 2800
TZ003 MO001 2850
TZ004 MO001 2900
TZ005 MO002 5000
TZ006 MO002 4500
TZ007 MO002 4000
TZ008 MO003 1000
TZ009 MO003 1000
我想查询成：
tz_no(通知单号) MO_NO（制令单号） QTY（数量）上工序数量
TZ001 MO001 3000 0
TZ002 MO001 2800 3000
TZ003 MO001 2850 2800
TZ004 MO001 2900 2900
TZ005 MO002 5000 0
TZ006 MO002 4500 5000
TZ007 MO002 4000 4500
TZ008 MO003 1000 0
TZ009 MO003 1000 1000

...全文

78 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

ilfang456 2010-03-18

打赏
举报

这么多方法，我学习了。不过我觉得还是第一个比较好理解

东那个升 2010-03-18

打赏
举报

--> 测试数据: @t

declare @t table (tz_no varchar(5),MO_NO varchar(5),QTY int)

insert into @t

select 'TZ001','MO001',3000 union all

select 'TZ002','MO001',2800 union all

select 'TZ003','MO001',2850 union all

select 'TZ004','MO001',2900 union all

select 'TZ005','MO002',5000 union all

select 'TZ006','MO002',4500 union all

select 'TZ007','MO002',4000 union all

select 'TZ008','MO003',1000 union all

select 'TZ009','MO003',1000



select a.*,isnull(b.QTY,0) as 上工序数量

from @t a left join    @t b on a.MO_NO=b.MO_NO and replace(a.tz_no,'TZ','')*1-1=replace(b.tz_no,'TZ','')*1 





(9 行受影响)

tz_no MO_NO         QTY       上工序数量

----- ----- ----------- -----------

TZ001 MO001        3000           0

TZ002 MO001        2800        3000

TZ003 MO001        2850        2800

TZ004 MO001        2900        2850

TZ005 MO002        5000           0

TZ006 MO002        4500        5000

TZ007 MO002        4000        4500

TZ008 MO003        1000           0

TZ009 MO003        1000        1000



(9 行受影响)

SQL77 2010-03-18

打赏
举报

--> 测试数据: @t

declare @t table (tz_no varchar(5),MO_NO varchar(5),QTY int)

insert into @t

select 'TZ001','MO001',3000 union all

select 'TZ002','MO001',2800 union all

select 'TZ003','MO001',2850 union all

select 'TZ004','MO001',2900 union all

select 'TZ005','MO002',5000 union all

select 'TZ006','MO002',4500 union all

select 'TZ007','MO002',4000 union all

select 'TZ008','MO003',1000 union all

select 'TZ009','MO003',1000



select A.*,ISNULL(B.QTY,0)QTY



from @t a LEFT JOIN @T B ON

 A.MO_NO=B.MO_NO 

AND RIGHT(B.tz_no,3)*1=RIGHT(A.tz_no,3)*1-1



（所影响的行数为 9 行）



tz_no MO_NO QTY         QTY         

----- ----- ----------- ----------- 

TZ001 MO001 3000        0

TZ002 MO001 2800        3000

TZ003 MO001 2850        2800

TZ004 MO001 2900        2850

TZ005 MO002 5000        0

TZ006 MO002 4500        5000

TZ007 MO002 4000        4500

TZ008 MO003 1000        0

TZ009 MO003 1000        1000



（所影响的行数为 9 行）

SQL77 2010-03-18

打赏
举报

--> 测试数据: @t

declare @t table (tz_no varchar(5),MO_NO varchar(5),QTY int)

insert into @t

select 'TZ001','MO001',3000 union all

select 'TZ002','MO001',2800 union all

select 'TZ003','MO001',2850 union all

select 'TZ004','MO001',2900 union all

select 'TZ005','MO002',5000 union all

select 'TZ006','MO002',4500 union all

select 'TZ007','MO002',4000 union all

select 'TZ008','MO003',1000 union all

select 'TZ009','MO003',1000



select *,ISNULL(

(SELECT SUM(Qty) FROM @t b WHERE MO_NO=A.MO_NO AND a.tz_no > tz_no),0)AS SUMM

from @t a



（所影响的行数为 9 行）



tz_no MO_NO QTY         SUMM        

----- ----- ----------- ----------- 

TZ001 MO001 3000        0

TZ002 MO001 2800        3000

TZ003 MO001 2850        5800

TZ004 MO001 2900        8650

TZ005 MO002 5000        0

TZ006 MO002 4500        5000

TZ007 MO002 4000        9500

TZ008 MO003 1000        0

TZ009 MO003 1000        1000



（所影响的行数为 9 行）

--小F-- 2010-03-18

打赏
举报

[Quote=引用 1 楼 fanzhouqi 的回复:]
SQL code

--> 测试数据: @t
declare @t table (tz_no varchar(5),MO_NO varchar(5),QTY int)
insert into @t
select 'TZ001','MO001',3000 union all
select 'TZ002','MO001',2800 union all
select 'TZ003','MO……
[/Quote]
up

fanzhouqi 2010-03-18

打赏
举报



--> 测试数据: @t

declare @t table (tz_no varchar(5),MO_NO varchar(5),QTY int)

insert into @t

select 'TZ001','MO001',3000 union all

select 'TZ002','MO001',2800 union all

select 'TZ003','MO001',2850 union all

select 'TZ004','MO001',2900 union all

select 'TZ005','MO002',5000 union all

select 'TZ006','MO002',4500 union all

select 'TZ007','MO002',4000 union all

select 'TZ008','MO003',1000 union all

select 'TZ009','MO003',1000



select *,(SELECT ISNULL(SUM(Qty),0) FROM @t b WHERE a.tz_no > tz_no)

from @t a





tz_no MO_NO QTY                     

----- ----- ----------- ----------- 

TZ001 MO001 3000        0

TZ002 MO001 2800        3000

TZ003 MO001 2850        5800

TZ004 MO001 2900        8650

TZ005 MO002 5000        11550

TZ006 MO002 4500        16550

TZ007 MO002 4000        21050

TZ008 MO003 1000        25050

TZ009 MO003 1000        26050



（所影响的行数为 9 行）