讨论，海量数据查询、统计性能优化算法！

Question

看来你还是没有搞错 case when 的用法, 你找数据测试一下才提出疑问"我认为如果条件只有kind='合格品入库'还可以"

Answer 1

CREATE PROC dbo.p_qry
@B_Date datetime,
@E_Date datetime
AS
SELECT 物品编码,
上期结存 = SUM(CASE kind WHEN '上期结存' THEN qty ELSE 0 END),
废品入库数 = SUM(CASE kind WHEN '废品入库数' THEN qty ELSE 0 END),
合格品入库 = SUM(CASE kind WHEN '合格品入库' THEN qty ELSE 0 END),
供应商补货 = SUM(CASE kind WHEN '供应商补货' THEN qty ELSE 0 END),
成品出库 = SUM(CASE kind WHEN '成品出库' THEN qty ELSE 0 END),
生产耗用 = SUM(CASE kind WHEN '生产耗用' THEN qty ELSE 0 END),
本期结存 = SUM(CASE kind WHEN '本期结存' THEN qty ELSE 0 END)
FROM(
SELECT 物品编码, kind = '上期结存', qty
FROM 出入库表
WHERE 报销时间 < @B_Date
UNION ALL
SELECT 物品编码, kind, qty
FROM 出入库表
WHERE 报销时间 BETWEEN @B_Date AND @E_Date
)A
GROUP BY 物品编码
GO

Answer 2

抱歉：可能是我没有表达清楚。
我的出入库明细数据结构如下：
id    itemcode   shuliang Kind        FromCangKu      ToCangKu      createdate
1       A          100    废品入库     待检品库       废品库         2006-04-20
2       A          200    合格品入库    待检品库       成品库         2006-05-20
3       B          500    订单入库    　订单       　　原材料库         2006-05-20
当然还有一个库存表
itemcode cangku  shuliang   createdate
  A       废品库   100        2006-04-20
  A       成品库   200         2006-05-20
  B       原材料库 500        2006-05-20
如果按照这俩个表的数据统计我上述的出入库明细表，那么上期结存和本期结存该如何处理呢，
现在库存表有一万左右记录，出入库明细表数据大概有几十万，现在的统计方法上期结存和本期结存统计有问题，而且速度超慢。

Answer 3

补充一下，假设B是原材料，那么成品库里肯定是没有的，所以在存货基础表中有一个ItemType来区分是原材料、还是成品。所以我觉得您的语句是否得加上，例如：
成品出库 = SUM(CASE kind WHEN '成品出库' and itemtype<>'成品' THEN qty ELSE 0 END),
SQL技术不精，有不对之处还请包涵。

Answer 4

假设B是原材料，那么成品库里肯定是没有的，所以在存货基础表中有一个ItemType来区分是原材料、还是成品。所以我觉得您的语句是否得加上，例如：
成品出库 = SUM(CASE kind WHEN '成品出库' and itemtype<>'成品' THEN qty ELSE 0 END),按你的描述, 自然是要加上的, 而你写的也是可行的语句, 只是你之前并没有描述这个需求, 所以当然无法给你写上这个条件.

Answer 5

你的问题现在看得还不是很清楚.比如, 计算的时候
  废品入库数, 难道对于同一itemcod, 即有废品的, 又有成品的? 如果是, 那计算的时候, 按你的表结构, 似乎是考虑  ToCangKu , 看它在那个仓库?
至于你后面补充的, ItemType, 这个列在你描述的结构中根本不曾出现还有你的库存表, createdate决定在这个时间时的库存?
其实问问题一定要把需求描述清楚, 而且一定要与你的实际需求贴切, 或者你有把握能把别人的回复应用于你的实际需求中, 否则大家都在那里猜, 这样就没有意思了.

Answer 6

废品入库数, 难道对于同一itemcod, 即有废品的, 又有成品的? 如果是, 那计算的时候, 按你的表结构, 似乎是考虑  ToCangKu , 看它在那个仓库?
  您的这个理解是很对的，很抱歉，需求总是无法描述清楚。
  试试以这样的方式描述一下:
表名：tbl_MoveDetail
功能：存储出入库的明细
id    itemcode   shuliang Kind        FromCangKu      ToCangKu      createdate
1       A          100    废品入库     待检品库       废品库         2006-04-20
2       A          200    合格品入库    待检品库       成品库         2006-05-20
3       B          500    订单入库    　订单       　　原材料库         2006-05-20
表名：tbl_KuCun
功能：库存表，存储物品在各仓库的当前库存情况
itemcode cangku  shuliang   createdate
  A       废品库   100        2006-04-20
  A       成品库   200         2006-05-20
  B       原材料库 500        2006-05-20要求：
   根据tbl_MoveDetail及tbl_KuCun统计某段日期内物品出入库移动情况。
   报表格式如下：
   其中：
   上期结存：截止到开始日期(2006-06-05)的库存数
   废品入库数: tbl_MoveDetail.kind=废品入库的库存移动数量
   合格品入库: tbl_MoveDetail.kind=合格品入库的库存移动数量
   ……
                  出入库统计表
日期：2006-06-05至2006-06-15   库位：全部仓库
物品编码  上期结存  废品入库数  合格品入库  供应商补货  成品出库   生产耗用 本期结存
   A         100       5            95          5          50         20      135   现在的问题是：
   1，上期结存如何统计
   2，我现在各个报表列基本都能统计出来，但是照前边所说的，统计效率比较差，不知道您提供的方法是否可以提升效率。
   家里的机器上没有数据库，所以没法试验您的方法，计划明天去单位试验一下。