求教这样的sql语句怎样写

yidichaxiang 2009-09-03 09:06:42
表A (时间,规格,数量,入库价格,核算价格)
表B (规格,库存价格,库存数量)

按时间排序,
表A 中 存的是当前结算时间段内的入库记录;
表B 中 存的是前一结算时间段的结算数量,价格;若没有对应表A中的规格,库存数量,库存价格都认为是0
现在想结算当前前结算时间段的结算数量,价格;

如 :
表A 时间,规格,数量,入库价格,核算价格
2009-09-01 13:20:40 A 23 134.34 0
2009-09-01 14:20:40 B 23 134.34 0
2009-09-01 15:20:40 A 23 134.34 0
2009-09-01 16:20:40 C 23 134.34 0
2009-09-02 13:20:40 A 23 134.34 0
2009-09-03 13:20:40 A 23 134.34 0

表B 规格,库存价格,库存数量
A 123.456 23
B 123.456 23
C 123.456 23

算法是:

当所计算的规格是第一次时从表B中读取数据 核算价格=(数量*入库价格+库存价格*库存数量)/(库存数量+数量)
若不是就从此规格的上一次计算结果读值
核算价格=(数量*入库价格+上一次核算价格*上一次库存数量)/(上一次库存数量+数量)

最好能没结算一条表A的记录就能更新表B :库存价格=核算价格;库存数量=库存数量+数量
这样就能始终使用 核算价格=(数量*入库价格+库存价格*库存数量)/(库存数量+数量)

除了游标,有没有其它方法





...全文
206 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
csdyyr 2009-09-03
  • 打赏
  • 举报
回复
DECLARE @TA TABLE([时间] DATETIME, [规格] VARCHAR(1), [数量] INT, [入库价格] DECIMAL(18,3), [核算价格] DECIMAL(18,3))
INSERT @TA
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0

DECLARE @TB TABLE([规格] VARCHAR(1), [库存价格] DECIMAL(18,3), [库存数量] INT)
INSERT @TB
SELECT 'A', 123.456, 23 UNION ALL
SELECT 'B', 123.456, 23 UNION ALL
SELECT 'C', 123.456, 23

SELECT A.*,B.库存价格,B.库存数量 INTO # FROM @TA AS A JOIN @TB AS B ON A.规格=B.规格 ORDER BY A.规格,时间
CREATE CLUSTERED INDEX IX_SPE ON #(规格,时间)

DECLARE @规格 VARCHAR(1), @AMT DECIMAL(18,3),@QTY INT

UPDATE #
SET @AMT=CASE WHEN @规格=规格 THEN @AMT+数量*入库价格 ELSE 数量*入库价格+库存数量*库存价格 END,
@QTY=CASE WHEN @规格=规格 THEN @QTY+数量 ELSE 数量+库存数量 END,
@规格=规格,
核算价格=@AMT/@QTY

SELECT 时间,规格,数量,入库价格,核算价格
FROM #

DROP TABLE #
/*
时间 规格 数量 入库价格 核算价格
------------------------------------------------------ ---- ----------- -------------------- --------------------
2009-09-01 13:20:40.000 A 23 134.340 128.898
2009-09-01 15:20:40.000 A 23 134.340 130.712
2009-09-02 13:20:40.000 A 23 134.340 131.619
2009-09-03 13:20:40.000 A 23 134.340 132.163
2009-09-01 14:20:40.000 B 23 134.340 128.898
2009-09-01 16:20:40.000 C 23 134.340 128.898

*/
guguda2008 2009-09-03
  • 打赏
  • 举报
回复
自我检讨一下,为了抢分不顾代码质量,库存价值是不应该用SUM的,呵呵


IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
DATE DATETIME,
GUIGE VARCHAR(50),
QUANTITY INT,
RPRICE NUMERIC(19,6),
HPRICE NUMERIC(19,6)
)
--SELECT 5929.308000/46
INSERT INTO A
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0
CREATE TABLE B(
GUIGE VARCHAR(50),
KPRICE NUMERIC(19,6),
KQUANTITY INT,
)
INSERT INTO B
select 'A', 123.456, 23 UNION ALL
select 'B', 123.456, 23 UNION ALL
select 'C', 123.456, 23
/*
SELECT * FROM A
SELECT * FROM B
*/

SELECT A1.GUIGE,A1.DATE
,SUM(A1.QUANTITY*A1.RPRICE)+MAX(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)) '核算价值'
,(SUM(A1.QUANTITY)+MAX(ISNULL(B.KQUANTITY,0))) '核算数量'
,(SUM(A1.QUANTITY*A1.RPRICE)+MAX(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)))
/(SUM(A1.QUANTITY)+MAX(ISNULL(B.KQUANTITY,0))) '核算价格'
FROM A A1
INNER JOIN A A2 ON A1.GUIGE=A2.GUIGE AND A1.DATE>=A2.DATE
LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
/*
A 2009-09-01 13:20:40.000 5929.308000 46 128.898000
A 2009-09-01 15:20:40.000 9019.128000 69 130.712000
A 2009-09-02 13:20:40.000 12108.948000 92 131.619000
A 2009-09-03 13:20:40.000 15198.768000 115 132.163200
B 2009-09-01 14:20:40.000 5929.308000 46 128.898000
C 2009-09-01 16:20:40.000 5929.308000 46 128.898000
*/


yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
这是我想要的结果,有没有效率高的算法
之前的数据都是从多个表中读出,数据量不小,且随日期不断增长
yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复

select a.GUIGE,a.DATE,(hssl+ISNULL(B.KQUANTITY,0)) as hssl,
(hsjg*hssl+ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0))/(hssl+ISNULL(B.KQUANTITY,0)) as hsje
from (
SELECT A1.GUIGE,A1.DATE
,SUM(A2.QUANTITY*A2.RPRICE)/sum(A2.QUANTITY) as hsjg
,SUM(A2.QUANTITY) as hssl
FROM A A1
INNER JOIN A A2
ON A1.GUIGE=A2.GUIGE
AND A1.DATE>=A2.DATE
--LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
) as a
left join b ON a.GUIGE=B.GUIGE
order by a.GUIGE,a.DATE



yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
SELECT A1.*,b.*
--,SUM(A2.QUANTITY*A2.RPRICE)+SUM(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)) '核算价格'
--into #aa

FROM A A1
INNER JOIN A A2
ON A1.GUIGE=A2.GUIGE
AND A1.DATE>=A2.DATE
LEFT JOIN B ON A1.GUIGE=B.GUIGE
--GROUP BY A1.GUIGE,A1.DATE
order by A1.GUIGE,A1.DATE

中 对应B表 的KQUANTITY,KPRICE
第 3,5,6,8,9,10行,被重复计算,即B表 的KQUANTITY,KPRICE在每次求 核算价格时只能被加一次
guguda2008 2009-09-03
  • 打赏
  • 举报
回复
[Quote=引用 15 楼 yidichaxiang 的回复:]
回复: guguda2008

'核算价格' 是 (数量*入库价格+库存价格*库存数量)/(库存数量+数量)


select 'A' as GUIGE,(134.34*23+123.456*23)/(23+23) as KPRICE,(23+23) as KQUANTITY
union
select 'A',(134.34*23+134.34*23+123.456*23)/(23+23+23),(23+23+23)
union
select 'A',(134.34*23+134.34*23+134.34*23+123.456*23)/(23+23+23+23),(23+23+23+23)
union
select 'A',(134.34*23+134.34*23+134.34*23+134.34*23+123.456*23)/(23+23+23+23+23),(23+23+23+23+23)



[/Quote]
我再看看。。。
yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
回复: guguda2008

'核算价格' 是 (数量*入库价格+库存价格*库存数量)/(库存数量+数量)


select 'A' as GUIGE,(134.34*23+123.456*23)/(23+23) as KPRICE,(23+23) as KQUANTITY
union
select 'A',(134.34*23+134.34*23+123.456*23)/(23+23+23),(23+23+23)
union
select 'A',(134.34*23+134.34*23+134.34*23+123.456*23)/(23+23+23+23),(23+23+23+23)
union
select 'A',(134.34*23+134.34*23+134.34*23+134.34*23+123.456*23)/(23+23+23+23+23),(23+23+23+23+23)





csdyyr 2009-09-03
  • 打赏
  • 举报
回复
想要的结果?
guguda2008 2009-09-03
  • 打赏
  • 举报
回复
LZ看看算出来的数对不对
guguda2008 2009-09-03
  • 打赏
  • 举报
回复

IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
DATE DATETIME,
GUIGE VARCHAR(50),
QUANTITY INT,
RPRICE NUMERIC(19,6),
HPRICE NUMERIC(19,6)
)
--SELECT 23*134.34+123.456*23
INSERT INTO A
SELECT '2009-09-01 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 14:20:40', 'B', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 15:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-01 16:20:40', 'C', 23, 134.34, 0 UNION ALL
SELECT '2009-09-02 13:20:40', 'A', 23, 134.34, 0 UNION ALL
SELECT '2009-09-03 13:20:40', 'A', 23, 134.34, 0
CREATE TABLE B(
GUIGE VARCHAR(50),
KPRICE NUMERIC(19,6),
KQUANTITY INT,
)
INSERT INTO B
select 'A', 123.456, 23 UNION ALL
select 'B', 123.456, 23 UNION ALL
select 'C', 123.456, 23
/*
SELECT * FROM A
SELECT * FROM B
*/

SELECT A1.GUIGE,A1.DATE
,SUM(A2.QUANTITY*A2.RPRICE)+SUM(ISNULL(B.KPRICE,0)*ISNULL(B.KQUANTITY,0)) '核算价格'
FROM A A1
INNER JOIN A A2 ON A1.GUIGE=A2.GUIGE AND A1.DATE>=A2.DATE
LEFT JOIN B ON A1.GUIGE=B.GUIGE
GROUP BY A1.GUIGE,A1.DATE
/*
A 2009-09-01 13:20:40.000 5929.308000
A 2009-09-01 15:20:40.000 11858.616000
A 2009-09-02 13:20:40.000 17787.924000
A 2009-09-03 13:20:40.000 23717.232000
B 2009-09-01 14:20:40.000 5929.308000
C 2009-09-01 16:20:40.000 5929.308000
*/

yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
若有效率好的算法,我可以再开帖加分,
htl258_Tony 2009-09-03
  • 打赏
  • 举报
回复
Mark
yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
对于规格A
2009-09-01 13:20:40 A 23 134.34 0
2009-09-01 15:20:40 A 23 134.34 0
2009-09-02 13:20:40 A 23 134.34 0

第一次是:
选读表B 123.456 23

核算价格=(123.456*23+23*134.34)/(23+23)
当前数量=23+23
更新表

第n次

核算价格=(上一次核算价格*当前数量+23*134.34)/(当前数量+23)
当前数量=当前数量+23

例:
第二次

核算价格=((123.456*23+23*134.34)*46+23*134.34)/(46+23)
当前数量=46+23



lihan6415151528 2009-09-03
  • 打赏
  • 举报
回复
直接用update可以了。
csdyyr 2009-09-03
  • 打赏
  • 举报
回复
直接用update可以了。
guguda2008 2009-09-03
  • 打赏
  • 举报
回复
一次更新不行吗?为什么要多次更新?
yidichaxiang 2009-09-03
  • 打赏
  • 举报
回复
现在我是这样算的:
先按 规格,时间排序
2009-09-01 13:20:40 A 23 134.34 0
2009-09-01 15:20:40 A 23 134.34 0
2009-09-02 13:20:40 A 23 134.34 0
2009-09-03 13:20:40 A 23 134.34 0
2009-09-01 14:20:40 B 23 134.34 0
2009-09-01 16:20:40 C 23 134.34 0

定义变量 @vargg 来存放 规格 ,初始化为 '0'
    @hsjg 存放 核算价格,@hssl 存放 核算数量
在游标循环中:
首先判断 @vargg 与 当前记录中的规格是否相等 
相等 
@hsjg=(@hsjg*@hssl+ 数量*入库价格)/(@hssl+数量)
@hssl=(@hssl+数量)
不相等
@hsjg=(数量*入库价格+库存价格*库存数量)/(库存数量+数量)
@hssl=(库存数量+数量)

@vargg =当前记录中的规格

进入下一次循环

问:
能不能在每次循环中把对应的值更新到表中

注: Ms-sql 2005

guguda2008 2009-09-03
  • 打赏
  • 举报
回复
小F早
guguda2008 2009-09-03
  • 打赏
  • 举报
回复
晕,发完题就跑了
--小F-- 2009-09-03
  • 打赏
  • 举报
回复
楼主 怎么判断什么时候是第一次从B表里读数据 什么时候不是第一次?
加载更多回复(1)

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧