使用Excel查询ERP数据库数据（Ms Query）之二

fuyou1355

销售等各部门需要及时查看ERP销售订单的执行情况——接单量、未发货情况等等数据，此需求可以使用excel查询ERP数据库（Ms Query）的功能解决！
代码见下：

ERP销售订单自动刷新表

版本1.1  销售订单主表

Select  so_somain.cSTCode "销售类型编码",so_somain.ID "销售订单主表标识",so_somain.cBusType "业务类型",so_somain.dDate "单据日期",so_somain.cSOCode "销售订单号",so_somain.cCusCode "客户编码",so_somain.cCusName "客户名称",so_somain.cDepCode "部门编码",so_somain.cPersonCode "业务员编码",so_somain.cexch_name "币种名称",so_somain.iExchRate "汇率",so_somain.iTaxRate "表头税率",so_somain.cMemo "备注",so_somain.cMaker "制单人",so_somain.cVerifier "审核人",so_somain.cCloser "关闭人",so_somain.dPreDateBT "预发货日期",so_somain.cCrmPersonCode "联系人编码",so_somain.cCrmPersonName "联系人"

From ufdata_001_2015.dbo.so_somain

版本1.2  销售订单子表

SELECT so_sodetails.cSOCode AS '销售订单号', so_sodetails.cInvCode AS '存货编码', inventory.cInvName AS '存货名称', so_sodetails.dPreDate AS '预发货日期', so_sodetails.iQuantity AS '数量', so_sodetails.iUnitPrice AS '原币无税单价', so_sodetails.iTaxUnitPrice AS '原币含税单价', so_sodetails.iMoney AS '原币无税金额', so_sodetails.iTax AS '原币税额', so_sodetails.iSum AS '原币价税合计', so_sodetails.iDisCount AS '原币折扣额', so_sodetails.iNatUnitPrice AS '本币无税单价', so_sodetails.iNatMoney AS '本币无税金额', so_sodetails.iNatTax AS '本币税额', so_sodetails.iNatSum AS '本币价税合计', so_sodetails.iNatDisCount AS '本币折扣额', so_sodetails.iFHQuantity AS '累计发货数量', so_sodetails.iFHMoney AS '累计原币发货金额', so_sodetails.iKPQuantity AS '累计开票数量', so_sodetails.iKPMoney AS '累计原币开票金额', so_sodetails.iTaxRate AS '税率', so_sodetails.cCusInvCode AS '客户存货编码', so_sodetails.cCusInvName AS '客户存货名称'

FROM ufdata_001_2015.dbo.inventory inventory, ufdata_001_2015.dbo.so_sodetails so_sodetails

WHERE so_sodetails.cInvCode = inventory.cInvCode

版本1.3  销售订单自动刷新表

SELECT so_somain.cSTCode AS '销售类型编码', so_somain.ID AS '销售订单主表标识', so_somain.cBusType AS '业务类型', so_somain.dDate AS '单据日期', so_somain.cSOCode AS '销售订单号', so_somain.cCusCode AS '客户编码', so_somain.cCusName AS '客户名称', so_somain.cDepCode AS '部门编码', so_somain.cPersonCode AS '业务员编码', so_somain.cexch_name AS '币种名称', so_somain.iExchRate AS '汇率', so_somain.iTaxRate AS '表头税率', so_somain.cMemo AS '备注', so_somain.cMaker AS '制单人', so_somain.cVerifier AS '审核人', so_somain.cCloser AS '关闭人', so_somain.dPreDateBT AS '预发货日期', so_somain.cCrmPersonCode AS '联系人编码', so_somain.cCrmPersonName AS '联系人', so_sodetails.cInvCode AS '存货编码', inventory.cInvName AS '存货名称', so_sodetails.dPreDate AS '预发货日期', so_sodetails.iQuantity AS '数量', so_sodetails.iUnitPrice AS '原币无税单价', so_sodetails.iTaxUnitPrice AS '原币含税单价', so_sodetails.iMoney AS '原币无税金额', so_sodetails.iTax AS '原币税额', so_sodetails.iSum AS '原币价税合计', so_sodetails.iDisCount AS '原币折扣额', so_sodetails.iNatUnitPrice AS '本币无税单价', so_sodetails.iNatMoney AS '本币无税金额', so_sodetails.iNatTax AS '本币税额', so_sodetails.iNatSum AS '本币价税合计', so_sodetails.iNatDisCount AS '本币折扣额', so_sodetails.iFHQuantity AS '累计发货数量', so_sodetails.iFHMoney AS '累计原币发货金额', so_sodetails.iKPQuantity AS '累计开票数量', so_sodetails.iKPMoney AS '累计原币开票金额', so_sodetails.iTaxRate AS '税率'

FROM ufdata_001_2015.dbo.inventory inventory, ufdata_001_2015.dbo.so_sodetails so_sodetails, ufdata_001_2015.dbo.so_somain so_somain

WHERE so_sodetails.cInvCode = inventory.cInvCode  and  so_somain.cSOCode = so_sodetails.cSOCode

版本1.4  销售订单自动刷新表

--添加排序

SELECT so_somain.cSTCode AS '销售类型编码', so_somain.ID AS '销售订单主表标识', so_somain.cBusType AS '业务类型', so_somain.dDate AS '单据日期', so_somain.cSOCode AS '销售订单号', so_somain.cCusCode AS '客户编码', so_somain.cCusName AS '客户名称', so_somain.cDepCode AS '部门编码', so_somain.cPersonCode AS '业务员编码', so_somain.cexch_name AS '币种名称', so_somain.iExchRate AS '汇率', so_somain.iTaxRate AS '表头税率', so_somain.cMemo AS '备注', so_somain.cMaker AS '制单人', so_somain.cVerifier AS '审核人', so_somain.cCloser AS '关闭人', so_somain.dPreDateBT AS '预发货日期', so_somain.cCrmPersonCode AS '联系人编码', so_somain.cCrmPersonName AS '联系人', so_sodetails.cInvCode AS '存货编码', inventory.cInvName AS '存货名称', so_sodetails.dPreDate AS '预发货日期', so_sodetails.iQuantity AS '数量', so_sodetails.iUnitPrice AS '原币无税单价', so_sodetails.iTaxUnitPrice AS '原币含税单价', so_sodetails.iMoney AS '原币无税金额', so_sodetails.iTax AS '原币税额', so_sodetails.iSum AS '原币价税合计', so_sodetails.iDisCount AS '原币折扣额', so_sodetails.iNatUnitPrice AS '本币无税单价', so_sodetails.iNatMoney AS '本币无税金额', so_sodetails.iNatTax AS '本币税额', so_sodetails.iNatSum AS '本币价税合计', so_sodetails.iNatDisCount AS '本币折扣额', so_sodetails.iFHQuantity AS '累计发货数量', so_sodetails.iFHMoney AS '累计原币发货金额', so_sodetails.iKPQuantity AS '累计开票数量', so_sodetails.iKPMoney AS '累计原币开票金额', so_sodetails.iTaxRate AS '税率'

FROM ufdata_001_2015.dbo.inventory inventory, ufdata_001_2015.dbo.so_sodetails so_sodetails, ufdata_001_2015.dbo.so_somain so_somain

WHERE so_sodetails.cInvCode = inventory.cInvCode AND so_somain.cSOCode = so_sodetails.cSOCode

order by so_somain.dDate

版本1.5  销售订单自动刷新表2016.11.01

--添加计算字段

SELECT so_somain.cSTCode AS '销售类型编码', so_somain.cBusType AS '业务类型', so_somain.dDate AS '单据日期', so_somain.cSOCode AS '销售订单号', so_somain.cCusCode AS '客户编码', so_somain.cCusName AS '客户名称', so_somain.cDepCode AS '部门编码', so_somain.cPersonCode AS '业务员编码', so_somain.cexch_name AS '币种名称', so_somain.iExchRate AS '汇率', so_somain.iTaxRate AS '表头税率', so_somain.cMemo AS '备注', so_somain.cMaker AS '制单人', so_somain.cVerifier AS '审核人', so_somain.cCloser AS '关闭人', so_somain.dPreDateBT AS '预发货日期', so_sodetails.dPreDate AS '预发货日期-01', so_somain.cCrmPersonCode AS '联系人编码', so_somain.cCrmPersonName AS '联系人', so_sodetails.cInvCode AS '存货编码', inventory.cInvName AS '存货名称', so_sodetails.iQuantity AS '数量', so_sodetails.iUnitPrice AS '原币无税单价', so_sodetails.iTaxUnitPrice AS '原币含税单价', so_sodetails.iMoney AS '原币无税金额', so_sodetails.iTax AS '原币税额', so_sodetails.iSum AS '原币价税合计', so_sodetails.iNatUnitPrice AS '本币无税单价', so_sodetails.iNatMoney AS '本币无税金额', so_sodetails.iNatTax AS '本币税额', so_sodetails.iNatSum AS '本币价税合计', so_sodetails.foutquantity AS '订单累计出库数量', (so_sodetails.iQuantity-isnull(so_sodetails.foutquantity,0)) AS '订单未出库数量', ((so_sodetails.iQuantity-isnull(so_sodetails.foutquantity,0))*so_sodetails.iNatUnitPrice) AS '订单未出库本币无税金额', so_sodetails.iKPQuantity AS '累计开票数量', so_sodetails.iKPMoney AS '累计原币开票金额', so_sodetails.iTaxRate AS '税率', (isnull(so_sodetails.foutquantity,0)-isnull(so_sodetails.iKPQuantity,0)) AS '已出库未开票数量', ((isnull(so_sodetails.foutquantity,0)-isnull(so_sodetails.iKPQuantity,0))*so_sodetails.iNatUnitPrice) AS '已出库未开票本币无税金额'

FROM ufdata_001_2015.dbo.inventory inventory, ufdata_001_2015.dbo.so_sodetails so_sodetails, ufdata_001_2015.dbo.so_somain so_somain

WHERE so_sodetails.cInvCode = inventory.cInvCode AND so_somain.cSOCode = so_sodetails.cSOCode

ORDER BY so_somain.dDate

zhuyangzhao0602

好好好！！！！！

open1991

感谢楼主分享

hujvjv

好东西谢谢

稳稳的幸福

真正的好东西啊

kenny122

好好好！！！！！

collegeaaaa

好好好好好好

xiaozi310

好，很好，非常好，贼他妈好！！！！！

ps7isy

这个的确是干货