制作库存交易明细表,比较有挑战性，邹健老兄有空进来帮我看看。

问题主题：制作库存交易明细表
表结构：
     tb1(品号，交易月份，期未库存）记录每个品号每个月的汇总结余。一个品号一个月只有一条记录，而且只有做了月结后才有数据。
     tb2(品号，交易日期，入出别(1.增加库存，2，減不库存)，交易数量) --记录每一笔交易的明细信息
问题：当制作库存交易明细表时，如果我以日期为条件查询某月的一段时间内(如2005-03-05~2005-03-16)或跨月(如2005-02-05~2005-03-06）的库存交易明细表时，报表要求有期初数，其期初数如何来求，大家在实际中有什么高效的解决方案。特别是tb2超过1千万条记录后，如何来提高这支报表的产生速度。

11 个解决方案

#1

SELECT A.入库别,A.SUM(数量) AS '交易数量' 
FROM 
(SELECT '期初库存' as '入库别',期未库存 AS '数量' 
FROM tb1 
where 交易月份 = 条件 
UNION ALL 
SELECT '期初库存' as '入库别',CASE WHEN 入出别 = 1 THEN 交易数量 ELSE -交易数量 END AS '数量' 
FROM tb2 
WHERE 交易日期 BETWEEN '时间1' AND '时间2') A 
UNION ALL 
SELECT 入库别,交易数量 
FROM tb2 
WHERE 交易日期 BETWEEN '时间3' AND '时间4'

#2

时间1与时间2是从月初到明细起始的日期
时间3与时间4是查询条件的时间段

#3

谢谢楼上的，还有别的吗？

#4

感觉数据库设计的不是太合理，随便说说

#5

楼上的，你有更好的结构吗？

#6

如果明细记录不能修改的话。可以把明细表改成三栏式的。


日期，入库数量。出库数据，库存数量。

#7

不行，明细表我可能会随时删除新增，这样一旦新增或删除又得重计库存数量，效率会不高。而且还会有并发的问题。

#8

别是tb2超过1千万条记录后，如何来提高这支报表的产生速度。

使用分区视图
分区视图允许将大型表中的数据拆分成较小的成员表。根据其中一列中的数据值范围，将数据在各个成员表之间进行分区。每个成员表的数据范围都在为分区列指定的 CHECK 约束中定义。然后定义一个视图，以使用 UNION ALL 将选定的成员表组合成单个结果集。引用该视图的 SELECT 语句为分区列指定搜索条件后，查询优化器将使用 CHECK 约束定义确定哪个成员表包含这些行。

例如，记录 1998 年销售额的 sales 表分区成 12 个成员表，每个月是一个成员表。每个成员表在 OrderMonth 列上都定义了约束：

CREATE TABLE May1998sales
   (OrderID      INT,
   CustomerID      INT      NOT NULL,
   OrderDate      DATETIME      NULL
      CHECK (DATEPART(yy, OrderDate) = 1998),
   OrderMonth      INT
      CHECK (OrderMonth = 5),
   DeliveryDate      DATETIME      NULL
      CHECK(DATEPART(mm, DeliveryDate) = 5)
   CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
   )

填充 May1998Sales 的应用程序必须确保所有行在 OrderMonth 列中均为 5，并且订购日期指定 1998 年 5 月的某个日期。该表上定义的约束强制实现这一要求。

然后定义一个视图，以使用 UNION ALL 选定全部 12 个成员表中的数据作为单个结果集：

CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales

例如，下面的 SELECT 语句查询特定月的信息。

SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892

SQL Server 查询优化器识别出此 SELECT 语句中的搜索条件只引用 May1998Sales 和 Jun1998Sales 表中的行，从而将其搜索范围限制在这些表上。

要在分区视图上执行更新，分区列必须是基表主键的一部分。如果视图不可更新，可以在允许更新的视图上创建 INSTEAD OF 触发器。应该在触发器中设计错误处理以确保不会插入重复的行。有关在视图上设计 INSTEAD OF 触发器的示例，请参见设计 INSTEAD OF 触发器。

分区视图返回正确的结果并不一定非要 CHECK 约束。但是，如果未定义 CHECK 约束，则查询优化器必须搜索所有表，而不是只搜索符合分区列上的搜索条件的表。如果不使用 CHECK 约束，则视图的操作方式与带有 UNION ALL 的任何其它视图相同。查询优化器不能对存储在不同表中的值作出任何假设，也不能跳过对参与视图定义的表的搜索。

如果分区视图所引用的所有成员表都在同一服务器上，则该视图是本地分区视图。如果成员表在多台服务器上，则该视图是分布式分区视图。分布式分区视图可用于在一组服务器间分布系统的数据库处理工作量。

分区视图使独立地维护成员表变得更容易。例如，在某个阶段结束时： 

可以更改当前结果的分区视图定义以添加最新的阶段和除去最早的阶段。


可以更改以前结果的分区视图定义以添加刚从当前结果视图中除去的阶段。也可以更新以前的结果视图以删除或存档该视图所包含的最早阶段。 
将数据插入到分区视图中后，就可以使用 sp_executesql 系统存储过程创建 INSERT 语句，该语句带有在有许多并发用户的系统中重新使用几率较高的执行计划。

#9

--这个一般用存储过程处理,方便推算
--大致的处理如下
create proc p_qry
@开始日期 datetime,
@结束日期 datetime
as
declare @年月1 int,@日期1 datetime
,@年月2 int,@日期2 datetime
--离开始日期最近的一次月结时间(这是为了处理不按时结转时,不能从tb1获得月结数据,需要推算期初的问题)
select @年月1=max(交易月份),@日期1=convert(char(6),@年月1)+'01'
from tb1
where 交易月份<convert(char(6),@开始日期,112)

select @年月2=max(交易月份),@日期2=convert(char(6),@年月2)+'01'
from tb1
where 交易月份<convert(char(6),@结束日期,112)


--期初数据
select 品号,标志='期初库存',交易数量=sum(交易数量)
from(
select 品号,期未库存 from tb1 where 交易月份=@年月1
union all
select 品号,case 入出别 when 1 then 交易数量 else -交易数量 end
from tb2
where (@日期1 is null or 交易日期>=@日期1) and 交易日期<@开始日期
)a group by 品号

--交易明细数据
union all
select 品号,标志=case 入出别 when 1 then '入库' else '出库' end,交易数量
from tb2
where 交易日期 between @开始日期 and @结束日期

--期末数据
union all
--期初数据
select 品号,标志='期末库存',交易数量=sum(交易数量)
from(
select 品号,期未库存 from tb1 where 交易月份=@年月2
union all
select 品号,case 入出别 when 1 then 交易数量 else -交易数量 end
from tb2
where (@日期2 is null or 交易日期>=@日期2) and 交易日期<=@结束日期
)a group by 品号

#10

邹老大的答案同我的一样.结贴.

#11

好强的邹建