ESAP第十三弹视图盛宴

系统表视图
数据整理视图
数据汇总视图
小结

ES也能实现类似编程中的MVC架构，基本原理就是通过ES维护数据库基表数据，基表数据生成数据库视图，ES读取视图完成UI显示。在这种循环结构中，存储和显示是分离的，从而使数据库能实现3NF(第三范式)。

本文将对sql视图所能实现的功能做一些演示，希望能启发读者进一步拓展ES后台功能。

系统表视图

系统连接状态

SELECT * FROM ESSystem..ES_seslog --可在工作台显示用户连接状态

自动任务清单，显示所有用户设置的自动任务。

SELECT B1.RtIds ASRtIds, B1.RtNames AS RtNames, B1.TaskId AS TaskId,
      B1.UserId ASUserId, A1.UserName AS UserName
FROM ES_User A1
INNER JOIN  ESSystem..ES_AFPlan B1 ON A1.UserId = B1.UserId

工作流状态，此略(详见ES工作流扫描)

数据整理视图

物料进出明细(第六弹有介绍),即多表结果集合并

SELECT B1.日期,B1.仓库,B1.单位部门,B2.描述,B1.批号,B1.入库,B1.出库,B2.单位,B1.备注,B2.物料编码,B2.物料分类
FROM (  SELECT 日期,仓库,交货方 AS 单位部门,物料编码,批号,数量 AS 入库,NULL AS 出库,备注
    FROM 入库单 A1,入库单_D A2 WHERE A1.EXCELSERVERRCID=A2.EXCELSERVERRCID
    UNION ALL
    SELECT 日期,仓库,发交,物料编码,批号,NULL,数量,备注
    FROM 出库单 A3,出库单_D A4 WHERE A3.EXCELSERVERRCID=A4.EXCELSERVERRCID) B1
INNER JOIN 物料表 B2 ON B1.物料编码=B2.物料编码

同表多列合并(一般来说要避免这种特殊设计)

SELECT 姓名1,工号1,部门1 FROM 培训记录明细
UNION ALL
SELECT 姓名2,工号2,部门2 FROM 培训记录明细

数据汇总视图

总库存/批次库存，此略（详见第六弹）
在途库存（单据未经确认的数据视为“在途”）

SELECT B2.描述,B1.数量 AS 在途数量,B2.单位,B2.物料编码,B2.物料分类
FROM (  SELECT 物料编码,SUM(数量) AS 数量
		FROM(	SELECT  物料编码,数量 FROM 入库单 A1,入库单_D A2 WHERE A1.EXCELSERVERRCID=A2.EXCELSERVERRCIDAND ISNULL(A1.确认人,’’)=’’
				UNION ALL
				SELECT 物料编码,-数量 FROM 出库单 A3,出库单_D A4 WHERE A3.EXCELSERVERRCID=A4.EXCELSERVERRCIDAND ISNULL(A3.确认人,’’)=’’
		) N
        GROUP BY  物料编码
) B1
RIGHT JOIN  物料表 B2 ON B1.物料编码=B2.物料编码

分组小计/合计

SELECT 仓库,物料分类,描述,数量,单位 FROM v_PCKC
UNION ALL
SELECT 仓库,物料分类 + '-小计' AS 物料分类,''AS描述,SUM(数量),单位
FROM V_PCKC GROUPBY 仓库,物料分类,单位
UNION ALL
SELECT 仓库+ '-合计','' AS 物料分类,''AS 描述,SUM(数量),单位
FROM V_PCKC GROUP BY 仓库,单位
ORDER BY 仓库,物料分类

移动平均价

 --视图1，带单价进出明细
SELECT 日期,物料编码,单价,数量 AS 收入,0 AS 发出
FROM 入库单 A1,入库单_D A2
WHERE A1.EXCELSERVERRCID= A2.EXCELSERVERRCID
UNION ALL
SELECT 日期,物料编码,0,0 AS 收入,数量 AS 发出
FROM 出库单 A1,出库单_D A2
WHERE A1.EXCELSERVERRCID= A2.EXCELSERVERRCID
--视图2，物料价格历史
SELECT 日期,
    物料编码,
    结存=ISNULL((SELECTSUM(收入)-SUM(发出) FROM VPRICEDWHERE 物料编码=A.物料编码 AND 日期<MIN(A.日期)),0),
    结存价=ISNULL((SELECT SUM(收入*单价)/(SUM(收入)) FROM VPRICED WHERE 物料编码=A.物料编码 AND 日期<MIN(A.日期)),0),
    SUM(收入)AS 收入,
    AVG(单价)AS 单价,
    SUM(发出)AS 发出
FROM VPRICED A
GROUP BY 日期,物料编码
--视图3，物料均价
select 日期,物料编码,
    均价=(结存*结存价+收入*单价)/(结存+收入)
FROM vPriceHis

无级BOM，此略（详见第九弹）。
订单状态切换*(该部分仅做演示)

SELECT DNO,T2S.S,CDATE FROM S,T2S WHERE S.V=T2S.CTL ANDS.T=T2S.TID
UNION ALL
SELECT ONO,T2S.S,ODATE FROM ODA,T2S WHERE ODA.S=0 ANDODA.V=T2S.CTL AND ODA.T=T2S.TID
UNION ALL
SELECT ONO,STAT,CDATE FROM ODB WHERE ODB.S=0   --获取所有订单状态
SELECT DNO,MAX(S) AS S FROM VORDSTAT GROUP BY DNO –获取订单当前状态

小结

ES视图是友好的，可以直接写SQL语句，所以不需要借助VBA就能在后台完成大量运算。
当多个模板中使用同样的多个数据源表定义提数公式时，可以考虑定义视图简化提数。
当大量使用回写公式时，可以考虑换一下思路，用视图完成相同的功能往往比回写更稳定。
视图可以减轻数据库存储压力，并且所能实现的功能远超以上罗列项……

ESAP第十三弹 视图盛宴

系统表视图

数据整理视图

数据汇总视图

小结

近似文章

ESAP第十三弹视图盛宴