管家婆ERP V3Ⅱ同价调拔单生成金蝶财务软件K3 V10.3标准凭证引入的SQL select 查询语句

双& 括起来的变量要先替换后才能执行， left(k.usercode,3)=’021′  是限制查询仓库的范围，因为多家母子公司使用一套ERP，所以需要限制只查询某一家公司的数据

SELECT a.BillDate AS FDate, YEAR(a.BillDate) AS FYear, MONTH(a.BillDate) AS FPeriod,
      ‘记’ AS FGroupID, ‘1’ AS FNumber, ‘1243.02’ AS FAccountNum,
      ‘废纸类商品（项目核算）’ AS FAccountName, ‘RMB’ AS FCurrencyNum,
      ‘人民币’ AS FCurrencyName,
      CASE WHEN a.qty > 0 THEN a.Total ELSE a.total * – 1 END AS FAmountFor,
      CASE WHEN (a.qty) > 0 THEN a.total ELSE 0 END AS FDebit, CASE WHEN (a.qty)
            ‘NONE’ AS FCheckerID, ‘NONE’ AS FApproveID, ‘NONE’ AS FCashierID,
      ” AS FHandler, ‘*’ AS FSettleTypeID, ” AS FSettleNo,
      CONVERT(CHAR(10), i.billdate, 23) +’:’+i.BillCode + ‘,’ + p.FullName + ‘:’ + LTRIM(STR(a.Qty, 15, 3))
      + ‘吨;单价:’ + LTRIM(STR(a.Price, 15, 2))
      + ‘元/吨,’ + i.Comment + ‘；’ + LTRIM(i.explain) AS FExplanation, 0 AS FQuantity,
      ‘*’ AS FMeasureUnitID, 0 AS FUnitPrice, ” AS FReference, a.BillDate AS FTransDate,
      ” AS FTransNo, 0 AS FAttachments, 999 AS FSerialNum, ” AS FObjectName,
      ” AS FParameter, 1 AS FExchangeRate, 0 AS FEntryID,
case when left(k.usercode,8)=’00100104′ then ‘部门—‘ + k.UserCode
      + ‘—‘ + k.FullName + ‘||库位—02—成品仓’ else ‘部门—‘ + LEFT(k.UserCode, 6)
      + ‘—‘ + LEFT(k.FullName, 5) + ‘||库位—‘ + RIGHT(LTRIM(k.UserCode), 2)
      + ‘—‘ + RIGHT(LTRIM(k.FullName), 3) end AS FItem, 0 AS FPosted, ” AS FInternalInd,
      ” AS FCashFlow, k.UserCode AS Expr1, k.FullName AS Expr2, i.BillCode
FROM InOutstocktable a INNER JOIN
      Stock k ON k.typeId = a.KtypeId INNER JOIN
      ptype p ON p.typeId = a.PtypeId INNER JOIN
      BillIndex i ON i.BillNumberId = a.BillNumberId
WHERE (a.BillType = 17) AND (i.RedWord = 0) and a.billdate=’&上日&’ and (left(k.usercode,3)=’021′ or left(k.usercode,3)=’001′)
ORDER BY a.BillDate, i.BillCode, k.UserCode

文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树SQL高级技巧CTE和递归查询31438 人正在系统学习中