L
lb_icesea79
Unregistered / Unconfirmed
GUEST, unregistred user!
--请帮忙优化一下(采购部分用移动平均法)
--执行一下SQL脚本, 在商品采购表中插入几条记录(编码, 数量, 单价)编码=1就行了
--问题, 1.写的太没有效率, 请帮忙优化
-- 2.每执行一次只能更改一条记录的前半部分或后半部分比如, 只更改第二条的期初部分第二次改期末部分
-- 第三次改第三条的期初部分...(但运行多次后最终结果是正确的)
USE TEMPDB
GO
IF OBJECT_ID('商品采购表') IS NULL
BEGIN
CREATE TABLE [商品采购表] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[日期] [datetime] NOT NULL CONSTRAINT [DF_商品采购表_日期] DEFAULT (getdate()),
[编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[期初数量] [money] NOT NULL CONSTRAINT [DF__商品采购表__期初数量__3D5E1FD2] DEFAULT (0),
[期初价格] [money] NOT NULL CONSTRAINT [DF__商品采购表__期初价格__3C69FB99] DEFAULT (0),
[数量] [money] NOT NULL CONSTRAINT [DF_商品采购表_数量] DEFAULT (0),
[单价] [money] NOT NULL CONSTRAINT [DF_商品采购表_单价] DEFAULT (0),
[期末数量] [money] NOT NULL CONSTRAINT [DF__商品采购表__期末数量__3F466844] DEFAULT (0),
[期末价格] [money] NOT NULL CONSTRAINT [DF__商品采购表__期末价格__3E52440B] DEFAULT (0),
[已审核] [bit] NOT NULL CONSTRAINT [DF__商品采购表__已审核__403A8C7D] DEFAULT (1),
CONSTRAINT [PK_商品采购表] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF OBJECT_ID('商品销售表') IS NULL
BEGIN
CREATE TABLE [DBO].[商品销售表](
[ID] [INT] IDENTITY (1, 1) NOT NULL,
[日期] [DATETIME] NOT NULL DEFAULT (GETDATE()),
[编码] [VARCHAR] (50) NOT NULL DEFAULT '',
[数量] [MONEY] NOT NULL DEFAULT 0,
[单价] [MONEY] NOT NULL DEFAULT 0,
CONSTRAINT [PK_商品销售表] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
UPDATE 商品采购表 SET 商品采购表.期初数量 =
ISNULL((SELECT TOP 1 A.期末数量
FROM 商品采购表 A
WHERE A.编码=商品采购表.编码 AND
A.日期<商品采购表.日期
ORDER BY A.ID DESC), 0),
商品采购表.期初价格=
ISNULL((SELECT TOP 1 B.期末价格
FROM 商品采购表 B
WHERE B.编码=商品采购表.编码 AND
B.日期<商品采购表.日期
ORDER BY B.ID DESC), 0),
商品采购表.期末数量=商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC)),
商品采购表.期末价格=(商品采购表.期初价格*商品采购表.期初数量+
商品采购表.单价*商品采购表.数量)/(CASE(商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC))) WHEN 0 THEN 1 ELSE
((商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC)))) END)
WHERE 商品采购表.编码='1'
GO
--执行一下SQL脚本, 在商品采购表中插入几条记录(编码, 数量, 单价)编码=1就行了
--问题, 1.写的太没有效率, 请帮忙优化
-- 2.每执行一次只能更改一条记录的前半部分或后半部分比如, 只更改第二条的期初部分第二次改期末部分
-- 第三次改第三条的期初部分...(但运行多次后最终结果是正确的)
USE TEMPDB
GO
IF OBJECT_ID('商品采购表') IS NULL
BEGIN
CREATE TABLE [商品采购表] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[日期] [datetime] NOT NULL CONSTRAINT [DF_商品采购表_日期] DEFAULT (getdate()),
[编码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[期初数量] [money] NOT NULL CONSTRAINT [DF__商品采购表__期初数量__3D5E1FD2] DEFAULT (0),
[期初价格] [money] NOT NULL CONSTRAINT [DF__商品采购表__期初价格__3C69FB99] DEFAULT (0),
[数量] [money] NOT NULL CONSTRAINT [DF_商品采购表_数量] DEFAULT (0),
[单价] [money] NOT NULL CONSTRAINT [DF_商品采购表_单价] DEFAULT (0),
[期末数量] [money] NOT NULL CONSTRAINT [DF__商品采购表__期末数量__3F466844] DEFAULT (0),
[期末价格] [money] NOT NULL CONSTRAINT [DF__商品采购表__期末价格__3E52440B] DEFAULT (0),
[已审核] [bit] NOT NULL CONSTRAINT [DF__商品采购表__已审核__403A8C7D] DEFAULT (1),
CONSTRAINT [PK_商品采购表] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF OBJECT_ID('商品销售表') IS NULL
BEGIN
CREATE TABLE [DBO].[商品销售表](
[ID] [INT] IDENTITY (1, 1) NOT NULL,
[日期] [DATETIME] NOT NULL DEFAULT (GETDATE()),
[编码] [VARCHAR] (50) NOT NULL DEFAULT '',
[数量] [MONEY] NOT NULL DEFAULT 0,
[单价] [MONEY] NOT NULL DEFAULT 0,
CONSTRAINT [PK_商品销售表] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
UPDATE 商品采购表 SET 商品采购表.期初数量 =
ISNULL((SELECT TOP 1 A.期末数量
FROM 商品采购表 A
WHERE A.编码=商品采购表.编码 AND
A.日期<商品采购表.日期
ORDER BY A.ID DESC), 0),
商品采购表.期初价格=
ISNULL((SELECT TOP 1 B.期末价格
FROM 商品采购表 B
WHERE B.编码=商品采购表.编码 AND
B.日期<商品采购表.日期
ORDER BY B.ID DESC), 0),
商品采购表.期末数量=商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC)),
商品采购表.期末价格=(商品采购表.期初价格*商品采购表.期初数量+
商品采购表.单价*商品采购表.数量)/(CASE(商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC))) WHEN 0 THEN 1 ELSE
((商品采购表.数量+商品采购表.期初数量-
(SELECT ISNULL(SUM(商品销售表.数量), 0)
FROM 商品销售表
WHERE 商品销售表.编码=商品采购表.编码 AND
商品销售表.日期<=商品采购表.日期 AND
商品销售表.日期>=
(SELECT TOP 1 C.日期
FROM 商品采购表 C
WHERE C.编码=商品采购表.编码 AND
C.日期<商品采购表.日期
ORDER BY C.ID DESC)))) END)
WHERE 商品采购表.编码='1'
GO