库存表A(现有库存)
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001 N-07 L KG 800
B005 M01 O KG 500
.....
收货记录表B
PARTNO VENDNO SALESTYPE ACTQTY INDATE(收货时间)
A001 N-07 L 250 5/01
A001 N-07 L 250 6/10
A001 N-07 O 300 6/15
A001 N-07 L 300 6/25
A001 N-07 L 300 7/02
A001 N-66 L 250 7/08
A001 M88 L 280 7/1
B005 M01 O 300 5/05
B005 M01 O 200 6/01
...... 为实现后进先出的目的,根据库存量取出最近的收货记录
比如物料A001 N-07 L 库存量是800,则取出最近>=800数量的收货记录
A001 N-07 L 300 7/02
A001 N-07 L 300 6/25
A001 N-07 L 250 6/10
300+300+250>=800
条件:1 B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY 收货记录
A001 N-07 L KG 800 7/02 300 6/25 300 6/10 250
B005 M01 O KG 500 6/01 200 5/05 300 得到以上的结果就行,格式不限,谢谢
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001 N-07 L KG 800
B005 M01 O KG 500
.....
收货记录表B
PARTNO VENDNO SALESTYPE ACTQTY INDATE(收货时间)
A001 N-07 L 250 5/01
A001 N-07 L 250 6/10
A001 N-07 O 300 6/15
A001 N-07 L 300 6/25
A001 N-07 L 300 7/02
A001 N-66 L 250 7/08
A001 M88 L 280 7/1
B005 M01 O 300 5/05
B005 M01 O 200 6/01
...... 为实现后进先出的目的,根据库存量取出最近的收货记录
比如物料A001 N-07 L 库存量是800,则取出最近>=800数量的收货记录
A001 N-07 L 300 7/02
A001 N-07 L 300 6/25
A001 N-07 L 250 6/10
300+300+250>=800
条件:1 B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY 收货记录
A001 N-07 L KG 800 7/02 300 6/25 300 6/10 250
B005 M01 O KG 500 6/01 200 5/05 300 得到以上的结果就行,格式不限,谢谢
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001 N-07 L KG 800
B005 M01 O KG 500
我基础不好,今天要交这个东西,急 越急越想不出来类似的问题是
CREATE table #TB1([PARTNO] nvarchar(4),[VENDO] nvarchar(4),[UNIT] nvarchar(2),[QTY] int)
Insert #TB1
select N'A001',N'N-07',N'KG',1000 union all
select N'B005',N'N-66',N'KG',500
--SELECT * FROM #TB1CREATE table #TB2([PRDATE] nvarchar(4),[PARTNO] nvarchar(4),[PRICE] int,[UNTI] nvarchar(2),[QTY] int)
Insert #TB2
select N'5/01',N'A001',1,N'KG',250 union all
select N'6/10',N'A001',1,N'KG',200 union all
select N'6/15',N'A001',1,N'KG',100 union all
select N'6/25',N'A001',1,N'KG',300 union all
select N'7/02',N'A001',1,N'KG',300 union all
select N'7/08',N'A001',1,N'KG',250 union all
select N'7/12',N'A001',1,N'KG',280 union all
select N'5/05',N'B005',5,N'KG',300 union all
select N'6/01',N'B005',5,N'KG',200
--SELECT * FROM #TB2 --DROP TABLE #TB2SELECT * FROM #TB1 E
LEFT JOIN (SELECT D.* FROM #TB2 D,
( SELECT A.PARTNO,MAX(B.PRDATE) AS PRDATE
FROM #TB1 A
LEFT JOIN ( SELECT [PARTNO],[PRDATE] ,
(select sum([QTY])
from #TB2 where [PARTNO] = t.[PARTNO] and [PRDATE] >=t.[PRDATE] ) as QTY
from #TB2 t group by t.[PARTNO],t.[PRDATE]
) B on B.[PARTNO] = A.[PARTNO]
WHERE A.[QTY] <=B.[QTY] GROUP BY A.PARTNO) C
WHERE D.[PARTNO] = C.[PARTNO] AND D.[PRDATE]>=C.[PRDATE]
) F ON E.[PARTNO] = F.[PARTNO]
B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001 N-07 L KG 800
B005 M01 O KG 500
.....
收货记录表B
PARTNO VENDNO SALESTYPE ACTQTY INDATE(收货时间)
A001 N-07 L 250 5/01
A001 N-07 L 250 6/10
A001 N-07 O 300 6/15
A001 N-07 L 300 6/25
A001 N-07 L 300 7/02
A001 N-66 L 250 7/08
A001 M88 L 280 7/1
B005 M01 O 300 5/05
B005 M01 O 200 6/01
...... 为实现后进先出的目的,根据库存量取出最近的收货记录
比如物料A001 N-07 L 库存量是800,则取出最近>=800数量的收货记录
A001 N-07 L 300 7/02
A001 N-07 L 300 6/25
A001 N-07 L 250 6/10
300+300+250>=800
条件:1 B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY 收货记录
A001 N-07 L KG 800 7/02 300 6/25 300 6/10 250
B005 M01 O KG 500 6/01 200 5/05 300 得到以上的结果就行,格式不限,谢谢这个怎么实现
A001 N-07 L KG 800
B005 M01 O KG 500 ---------列數據\數據對不上吧?
A001 N-07 L KG 800
B005 M01 O KG 500
A001 N-07 L KG 800
B005 M01 O KG 500
a.*,b.[QTY] as B_Qty,b.[prdate] as B_prdate
from
Ta a,
(Select *,(select sum([QTY]) from Tb where [PARTNO]=a.[PARTNO] and [VENDNO]=a.[VENDNO] and a.[SALESTYPE]=[SALESTYPE] and [prdate]>=a.[prdate]) as sumQTY from Tb a)B
where
b.sumQTY-b.[QTY]<=a.[QTY] and a.[PARTNO]=b.[PARTNO] and a.[VENDNO]=b.[VENDNO] and a.[SALESTYPE]=b.[SALESTYPE]
order by a.[PARTNO]
這樣用效率高一些
我用EXCEL 里不支持函数 临时表 变量还有调用函数那局出错 谢谢了
不会吧
我这边测试都好好的啊。
导出到EXCEL里也好好的啊。
不了解你的意思!
找不到列 "DBO" 或用户定义的函数或聚合 "DBO.F_CHECKDT",或者名称不明确。
use MICA_INV_TEST
go
select *,LT=dbo.F_CHECKDT(PARTNO,VENDNO,QTY) from sys.objects WHERE NAME='TB'
select * from TB
Server: Msg 207, Level 16, State 1, Line 1
列名 'PARTNO' 无效。
Server: Msg 207, Level 16, State 1, Line 1
列名 'VENDNO' 无效。
Server: Msg 207, Level 16, State 1, Line 1
列名 'QTY' 无效。
create table tbA(partno varchar(10), PARTDESC varchar(10), VENDNO varchar(10), GRADE varchar(10), SALESTYPE varchar(10), UNIT varchar(10), QTY int)
create table tbB(PARTNO varchar(10), VENDNO varchar(10), SALESTYPE varchar(10), ACTQTY int, INDATE datetime)
insert into tbA select 'A001', '', 'N-07', '', 'L', 'KG', 800
insert into tbA select 'B005', '', 'M01', '', 'O', 'KG', 500
insert into tbB select 'A001', 'N-07', 'L', 250 , '2008-05-01'
insert into tbB select 'A001', 'N-07', 'L', 250 , '2008-06-10'
insert into tbB select 'A001', 'N-07', 'O', 300 , '2008-06-15'
insert into tbB select 'A001', 'N-07', 'L', 300 , '2008-06-25'
insert into tbB select 'A001', 'N-07', 'L', 300 , '2008-07-02'
insert into tbB select 'A001', 'N-66', 'L', 250 , '2008-07-08'
insert into tbB select 'A001', 'M88', 'L', 280 , '2008-07-01'
insert into tbB select 'B005', 'M01', 'O', 300 , '2008-05-05'
insert into tbB select 'B005', 'M01', 'O', 200 , '2008-06-01'
--drop function dbo.test
create function dbo.test(@partno varchar(10), @vendno varchar(10), @SALESTYPE varchar(10), @actqty int)
returns @tbtest table(partno varchar(10), PARTDESC varchar(10), VENDO varchar(10), GRADE varchar(10), SALESTYPE varchar(10), UNIT varchar(10), QTY int,memo varchar(200))
as
begin
declare @s int,@ss varchar(100)
select @s = 0, @ss = '';
select
@ss = @ss + ' ' + case when @s < 800 then convert(varchar(10),indate,120) + ' ' + rtrim(actqty) else '' end,
@s = @s + case when @s < @actqty then actqty else 0 end
from tbB where partno = @partno and vendno = @vendno and SALESTYPE = @SALESTYPE order by indate desc
--select @s,@ss
insert into @tbtest
select *,@ss from tbA where partno = @partno and vendno = @vendno and SALESTYPE = @SALESTYPE
return
end
select b.* from tba a CROSS APPLY
dbo.test(a.partno, a.vendno, SALESTYPE, qty) b
先说明,2005的不熟,
但第一感觉,你明显写错了吧!select *,LT=dbo.F_CHECKDT(PARTNO,VENDNO,QTY) from sys.objects WHERE NAME='TB'
你去查表结构
这跟你表里的信息有什么关联?难道是我理解错了?
sys.objects这不是系统表的?
select * from TB
Server: Msg 207, Level 16, State 1, Line 1
列名 'PARTNO' 无效。
Server: Msg 207, Level 16, State 1, Line 1
列名 'VENDNO' 无效。
Server: Msg 207, Level 16, State 1, Line 1
列名 'QTY' 无效。sys.objects:系统表里那三个列名当然是没有的。
怎么可能有。
我的客户端 MS2000 服务器 2005SELECT *,LT=DBO.F_CHECKDT(PARTNO,VENDO,QTY) FROM TB 你的这个是什么原因
Server: Msg 4121, Level 16, State 1, Line 1
找不到列 "DBO" 或用户定义的函数或聚合 "DBO.F_CHECKDT",或者名称不明确小弟非常感谢你的耐心指导 谢谢
DECLARE @TB1 table([PARTNO] nvarchar(4),[VENDO] nvarchar(4),[UNIT] nvarchar(2),[QTY] int)
Insert @TB1
select N'A001',N'N-07',N'KG',1000 union all
select N'B005',N'N-66',N'KG',500
--SELECT * FROM @TB1 DECLARE @TB2 table([PRDATE] nvarchar(4),[PARTNO] nvarchar(4),[PRICE] int,[UNTI] nvarchar(2),[QTY] int)
Insert @TB2
select N'5/01',N'A001',1,N'KG',250 union all
select N'6/10',N'A001',1,N'KG',200 union all
select N'6/15',N'A001',1,N'KG',100 union all
select N'6/25',N'A001',1,N'KG',300 union all
select N'7/02',N'A001',1,N'KG',300 union all
select N'7/08',N'A001',1,N'KG',250 union all
select N'7/12',N'A001',1,N'KG',280 union all
select N'5/05',N'B005',5,N'KG',300 union all
select N'6/01',N'B005',5,N'KG',200 --2005,
--使用xml+cross join方式,
SELECT H.PARTNO,H.VENDO,H.UNIT,H.PRICE,H.SUMQTY,STUFF(G.REMARK.value('/R[1]','nvarchar(max)'),1,1,'') AS '收货记录'
FROM
(
SELECT DISTINCT E.PARTNO,E.VENDO,F.PRICE,E.UNIT,E.QTY AS SUMQTY FROM @TB1 E
LEFT JOIN (SELECT D.* FROM @TB2 D,
( SELECT A.PARTNO,MAX(B.PRDATE) AS PRDATE
FROM @TB1 A
LEFT JOIN ( SELECT [PARTNO],[PRDATE] ,
(select sum([QTY])
from @TB2 where [PARTNO] = t.[PARTNO] and [PRDATE] >=t.[PRDATE] ) as QTY
from @TB2 t group by t.[PARTNO],t.[PRDATE]
) B on B.[PARTNO] = A.[PARTNO]
WHERE A.[QTY] <=B.[QTY] GROUP BY A.PARTNO) C
WHERE D.[PARTNO] = C.[PARTNO] AND D.[PRDATE]>=C.[PRDATE]
) F ON E.[PARTNO] = F.[PARTNO]
) H
CROSS APPLY
(
SELECT REMARK = (SELECT N' '+LTRIM(F.PRDATE)+' ' +LTRIM(F.QTY) FROM @TB1 E
LEFT JOIN (SELECT D.* FROM @TB2 D,
( SELECT A.PARTNO,MAX(B.PRDATE) AS PRDATE
FROM @TB1 A
LEFT JOIN ( SELECT [PARTNO],[PRDATE] ,
(select sum([QTY])
from @TB2 where [PARTNO] = t.[PARTNO] and [PRDATE] >=t.[PRDATE] ) as QTY
from @TB2 t group by t.[PARTNO],t.[PRDATE]
) B on B.[PARTNO] = A.[PARTNO]
WHERE A.[QTY] <=B.[QTY] GROUP BY A.PARTNO) C
WHERE D.[PARTNO] = C.[PARTNO] AND D.[PRDATE]>=C.[PRDATE]
) F ON E.[PARTNO] = F.[PARTNO]
WHERE E.[PARTNO] = H.[PARTNO] AND E.VENDO =H.VENDO AND E.UNIT = H.UNIT AND E.QTY = H.SUMQTY
For XML PATH(''), ROOT('R'), TYPE)
) G/*
PARTNO VENDO UNIT PRICE SUMQTY 收货记录
------ ----- ---- ----------- ----------- ------------------------------------
A001 N-07 KG 1 1000 6/25 300 7/02 300 7/08 250 7/12 280
B005 N-66 KG 5 500 5/05 300 6/01 200
*/
没有考虑效率问题,
自己尝试着在修改一下即可,
1,先产生列表,如果有效率更好的方式最好选择更好的方式,
看看18楼风的效率是否可以,
SELECT * FROM @TB1 E
LEFT JOIN (SELECT D.* FROM @TB2 D,
( SELECT A.PARTNO,MAX(B.PRDATE) AS PRDATE
FROM @TB1 A
LEFT JOIN ( SELECT [PARTNO],[PRDATE] ,
(select sum([QTY])
from @TB2 where [PARTNO] = t.[PARTNO] and [PRDATE] >=t.[PRDATE] ) as QTY
from @TB2 t group by t.[PARTNO],t.[PRDATE]
) B on B.[PARTNO] = A.[PARTNO]
WHERE A.[QTY] <=B.[QTY] GROUP BY A.PARTNO) C
WHERE D.[PARTNO] = C.[PARTNO] AND D.[PRDATE]>=C.[PRDATE]
) F ON E.[PARTNO] = F.[PARTNO]2.在根据2005中xml+cross join 方式合并对应的列,
SELECT A.A1,
ColumnName=STUFF(B.A2.VALUE('/R[1]','NVARCHAR(MAX)'),1,1,'')
FROM (SELECT DISTINCT A1 FROM TB) A
CROSS APPLY ( SELECT A2=(
SELECT N' '+A2 FROM TB WHERE A1=A.A1 FOR XML PATH(''), ROOT('R'), TYPE)
)B
PARTNO PARTDESC VENDO GRADE SALESTYPE UNIT QTY
A001 N-07 L KG 800
B005 M01 O KG 500
.....
收货记录表B
PARTNO VENDNO SALESTYPE ACTQTY INDATE(收货时间)
A001 N-07 L 250 5/01
A001 N-07 L 250 6/10
A001 N-07 O 300 6/15
A001 N-07 L 300 6/25
A001 N-07 L 300 7/02
A001 N-66 L 250 7/08
A001 M88 L 280 7/1
B005 M01 O 300 5/05
B005 M01 O 200 6/01
...... 为实现后进先出的目的,根据库存量取出最近的收货记录
比如物料A001 N-07 L 库存量是800,则取出最近>=800数量的收货记录
A001 N-07 L 300 7/02
A001 N-07 L 300 6/25
A001 N-07 L 250 6/10
300+300+250>=800
条件:1 B表的PARTNO VENDNO SALESTYPE 三个字段的值等于表A的
谢谢师傅
不过我的表不同
if not object_id('Tempdb..#Ta') is null
drop Table #Ta
Go
Create Table #Ta([PARTNO] nvarchar(4),[VENDNO] nvarchar(4),[SALESTYPE] nvarchar(1),[UNIT] nvarchar(2),[QTY] int)
Insert #Ta
select N'A001',N'N-07',N'L',N'KG',800 union all
select N'B005',N'M01',N'O',N'KG',500
Go
if not object_id('Tempdb..#Tb') is null
drop Table #Tb
Go
Create table #Tb([PARTNO] nvarchar(4),[VENDNO] nvarchar(4),[SALESTYPE] nvarchar(1),[ACTQTY] int,[INDATE] nvarchar(4))
Insert #Tb
select N'A001',N'N-07',N'L',250,N'5/01' union all
select N'A001',N'N-07',N'L',250,N'6/10' union all
select N'A001',N'N-07',N'O',300,N'6/15' union all
select N'A001',N'N-07',N'L',300,N'6/25' union all
select N'A001',N'N-07',N'L',300,N'7/02' union all
select N'A001',N'N-66',N'L',250,N'7/08' union all
select N'A001',N'M88',N'L',280,N'7/1' union all
select N'B005',N'M01',N'O',300,N'5/05' union all
select N'B005',N'M01',N'O',200,N'6/01'
Go
;with C
as
(select
a.*,b.[ACTQTY],b.[INDATE]
from
#Ta a,
(Select *,(select sum([ACTQTY]) from #Tb where [PARTNO]=a.[PARTNO] and [VENDNO]=a.[VENDNO] and a.[SALESTYPE]=[SALESTYPE] and [INDATE]>=a.[INDATE]) as sumQTY from #Tb a)B
where
b.sumQTY-b.[ACTQTY]<a.[QTY] and a.[PARTNO]=b.[PARTNO] and a.[VENDNO]=b.[VENDNO] and a.[SALESTYPE]=b.[SALESTYPE]
)
select
a.*,[收货记录]=stuff(b.Col.value('/R[1]','nvarchar(max)'),1,1,'')
from
#Ta a
outer apply
(select Col=(select ' '+[INDATE]+' '+rtrim([ACTQTY]) from C where [PARTNO]=a.[PARTNO] and [VENDNO]=a.[VENDNO] and [SALESTYPE]=a.[SALESTYPE] order by [INDATE] desc for xml path(''),ROOT('R'),Type))b PARTNO VENDNO SALESTYPE UNIT QTY 收货记录
------ ------ --------- ---- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A001 N-07 L KG 800 7/02 300 6/25 300 6/10 250
B005 M01 O KG 500 6/01 200 5/05 300
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.sumQTY"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.ACTQTY"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.PARTNO"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.VENDNO"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.SALESTYPE"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.ACTQTY"。
Server: Msg 4104, Level 16, State 1, Line 1
无法绑定由多个部分组成的标识符 "b.INDATE"。