SELECT A.cPOID, --采购订单号
(
SELECT cMaker FROM dbo.PO_Pomain WHERE cPOID = A.cPOID
) AS cMaker,--制单人
(
SELECT cVenName
FROM dbo.Vendor
WHERE cVenCode =
(
SELECT cVenCode FROM dbo.PO_Pomain WHERE cPOID = A.cPOID
)
) AS cVenName,--供应商名称
A.iQuantity, --采购订单数量合计
A.iNatSum, --本币价税合计
A.iInvQTY, --累计发票数量合计
A.iNatInvMoney, --累计本币发票金额
A.iTotal, --累计本币付款
A.SubQuantity, --欠票数量
A.SubNatSum --欠票金额
FROM
(
SELECT PO_Pomain.cPOID,
CAST(SUM(ISNULL(iQuantity, 0)) AS DECIMAL(18, 2)) AS iQuantity,--iQuantity字段为空(NULL),则将其视为0,然后使用SUM()函数来计算所有非空(或被转换为0的空值)iQuantity值的总和,将计算结果转换为最多有18位数字,其中小数点后最多2位的十进制数。
CAST(SUM(ISNULL(iNatSum, 0)) AS DECIMAL(18, 2)) AS iNatSum,
CAST(SUM(ISNULL(iInvQTY, 0)) AS DECIMAL(18, 2)) AS iInvQTY,
CAST(SUM(ISNULL(iNatInvMoney, 0)) AS DECIMAL(18, 2)) AS iNatInvMoney,
CAST(SUM(ISNULL(iTotal, 0)) AS DECIMAL(18, 2)) AS iTotal,
CAST((SUM(ISNULL(iQuantity, 0)) - SUM(ISNULL(iInvQTY, 0))) AS DECIMAL(18, 2)) AS SubQuantity,
CAST((SUM(ISNULL(iNatSum, 0)) - SUM(ISNULL(iNatInvMoney, 0))) AS DECIMAL(18, 2)) AS SubNatSum
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
GROUP BY dbo.PO_Pomain.cPOID
) A
WHERE A.SubNatSum > 0
用友U8未完成开票采购订单查询语句
文章解释了如何通过SQL查询汇总采购订单数据,包括制单人、供应商、数量、金额,特别关注了未结清款项(SubNatSum)。
摘要由CSDN通过智能技术生成