MSSQL里报错！无法绑定由多个部分组成的标识符,access没事，

大张 2014-01-14 12:14:33 
新：



SELECT     dbo.tGoods.TypeCode, dbo.tGoods.GoodsBrand, dbo.tGoods.GoodsType, dbo.tGoods.ifDisCount, dbo.tGoods.Supply, dbo.tGoods.Remark, dbo.tBill.BillID, 

    dbo.tBill.CustName, dbo.tBill.BillCode, dbo.tBill.AddDate, dbo.tBill.AddTime, dbo.tBill.MRemark, dbo.tBill.FUser, dbo.tBill.Maker, dbo.tBill.MakerDate, dbo.tBill.Editer, 

    dbo.tBill.EditDate, dbo.tBill.BillType, dbo.tBill.InOrOut, dbo.tBill.VipCode, dbo.tBill.PayType, dbo.tBill.SkMoney, dbo.tBill.ZLMoney, dbo.tBill.YSMoney, 

    dbo.tBill.DepotName, dbo.tBill.Field1, dbo.tBill.Field2, dbo.tBill.Field3, dbo.tBill.Field4, dbo.tBill.Field5, DATEPART(hh, dbo.tBill.AddTime) AS HJtime, 

    CAST(YEAR(dbo.tBill.AddDate) AS varchar(4)) + '-' + CAST(MONTH(dbo.tBill.AddDate) AS varchar(4)) AS HjMonth, DATEPART(yyyy, dbo.tBill.AddDate) AS HjYear, 

    DATEPART(ww, dbo.tBill.AddDate) AS WeekDate, '全部' AS AllBill, dbo.tBillDetail.BillDetailID, CAST(DATEPART(yyyy, dbo.tBill.AddDate) AS varchar(4)) 

    + '年' + CAST(DATEPART(q, dbo.tBill.AddDate) AS varchar(4)) + '季度' AS HjSeason, dbo.tVip.VipName, dbo.tBillDetail.BarCode, dbo.tBillDetail.DBillCode, 

    dbo.tBillDetail.GoodsStyle, dbo.tBillDetail.GoodsName, dbo.tBillDetail.Units, dbo.tBillDetail.SizeGroup, dbo.tBillDetail.ColorCode, dbo.tBillDetail.ColorName, 

    dbo.tBillDetail.ColorValue, dbo.tBillDetail.DetailField1, dbo.tBillDetail.DetailField2, dbo.tBillDetail.DetailField3, dbo.tBillDetail.DetailField4, dbo.tBillDetail.DetailField5, 

    dbo.tBillDetail.SizeCode, dbo.tBillDetail.SizeName, dbo.tBillDetail.Number AS Number1, dbo.tBillDetail.Money AS Money1, dbo.tBillDetail.InMoney AS InMoney1, 

    (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') THEN tBillDetail.Number * tBillDetail.Sign * - 1 ELSE tBillDetail.Number * tBillDetail.Sign END) AS Number, 

    dbo.tBillDetail.Discount, dbo.tBillDetail.Price, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 

    THEN tBillDetail.Money * tBillDetail.Sign * - 1 ELSE tBillDetail.Money * tBillDetail.Sign END) AS Money, dbo.tBillDetail.colPosition, dbo.tBillDetail.rowPosition, 

    dbo.tBillDetail.DRemark, dbo.tBillDetail.Sign, dbo.tBillDetail.InPrice, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 

    THEN tBillDetail.InPrice * tBillDetail.Number * tBillDetail.Sign * - 1 ELSE tBillDetail.InPrice * tBillDetail.Number * tBillDetail.Sign END) AS InMoney, 

    dbo.tGoods.dpPrice, dbo.tGoods.xsPrice, dbo.tGoods.GoodsSeason, dbo.tGoods.DepotUp, dbo.tGoods.DepotDown

FROM         dbo.tBill LEFT OUTER JOIN

    dbo.tBillDetail LEFT OUTER JOIN

    dbo.tGoods ON dbo.tBillDetail.GoodsStyle = dbo.tGoods.GoodsStyle ON dbo.tBill.BillCode = dbo.tBillDetail.DBillCode LEFT OUTER JOIN

    dbo.tVip ON dbo.tBill.VipCode = dbo.tVip.VipCode

UNION ALL

SELECT     tGoods_1.TypeCode, tGoods_1.GoodsBrand, tGoods_1.GoodsType, tGoods_1.ifDisCount, tGoods_1.Supply, tGoods_1.Remark, tBill_1.BillID, tBill_1.DepotName, 

    tBill_1.BillCode, tBill_1.AddDate, tBill_1.AddTime, tBill_1.MRemark, tBill_1.FUser, tBill_1.Maker, tBill_1.MakerDate, tBill_1.Editer, tBill_1.EditDate, tBill_1.BillType, 

    tBill_1.InOrOut, tBill_1.VipCode, tBill_1.PayType, tBill_1.SkMoney, tBill_1.ZLMoney, tBill_1.YSMoney, tBill_1.CustName, tBill_1.Field1, tBill_1.Field2, tBill_1.Field3, 

    tBill_1.Field4, tBill_1.Field5, DATEPART(hh, tBill_1.AddTime) AS HjTime, CAST(YEAR(tBill_1.AddDate) AS varchar(4)) + '-' + CAST(MONTH(tBill_1.AddDate) AS varchar(4))

     AS HjMonth, DATEPART(yyyy, tBill_1.AddDate) AS HjYear, DATEPART(ww, tBill_1.AddDate) AS WeekDate, '全部' AS AllBill, tBillDetail_1.BillDetailID, 

    CAST(DATEPART(yyyy, tBill_1.AddDate) AS varchar(4)) + '年' + CAST(DATEPART(q, tBill_1.AddDate) AS varchar(4)) + '季度' AS HjSeason, tVip_1.VipName, 

    tBillDetail_1.BarCode, tBillDetail_1.DBillCode, tBillDetail_1.GoodsStyle, tBillDetail_1.GoodsName, tBillDetail_1.Units, tBillDetail_1.SizeGroup, 

    tBillDetail_1.ColorCode, tBillDetail_1.ColorName, tBillDetail_1.ColorValue, tBillDetail_1.DetailField1, tBillDetail_1.DetailField2, tBillDetail_1.DetailField3, 

    tBillDetail_1.DetailField4, tBillDetail_1.DetailField5, tBillDetail_1.SizeCode, tBillDetail_1.SizeName, tBillDetail_1.Number AS Number1, 

    tBillDetail_1.Money AS Money1, tBillDetail_1.InMoney AS InMoney1, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 

    THEN tBillDetail.Number * 1 * - 1 ELSE tBillDetail.Number * 1 END) AS Number, tBillDetail_1.Discount, tBillDetail_1.Price, (CASE WHEN tBill.BillType IN ('销售出货', 

    '销售退货') THEN tBillDetail.Money * 1 * - 1 ELSE tBillDetail.Money * 1 END) AS Money, tBillDetail_1.colPosition, tBillDetail_1.rowPosition, tBillDetail_1.DRemark, 

    1 AS Expr1, tBillDetail_1.InPrice, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 

    THEN tBillDetail.InPrice * tBillDetail.Number * 1 * - 1 ELSE tBillDetail.InPrice * tBillDetail.Number * 1 END) AS InMoney, tGoods_1.dpPrice, tGoods_1.xsPrice, 

    tGoods_1.GoodsSeason, tGoods_1.DepotUp, tGoods_1.DepotDown

FROM         dbo.tBill AS tBill_1 LEFT OUTER JOIN

    dbo.tBillDetail AS tBillDetail_1 LEFT OUTER JOIN

    dbo.tGoods AS tGoods_1 ON tBillDetail_1.GoodsStyle = tGoods_1.GoodsStyle ON tBill_1.BillCode = tBillDetail_1.DBillCode LEFT OUTER JOIN

    dbo.tVip AS tVip_1 ON tBill_1.VipCode = tVip_1.VipCode

WHERE     (tBill_1.BillType = '店铺调拨')
...全文
163 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
帮你排个序方便你找，另外，你点SSMS那个勾，就会有错误提示，你双击那个提示，有些情况下是可以定位错误位置的 
SELECT  dbo.tGoods.TypeCode ,
        dbo.tGoods.GoodsBrand ,
        dbo.tGoods.GoodsType ,
        dbo.tGoods.ifDisCount ,
        dbo.tGoods.Supply ,
        dbo.tGoods.Remark ,
        dbo.tBill.BillID ,
        dbo.tBill.CustName ,
        dbo.tBill.BillCode ,
        dbo.tBill.AddDate ,
        dbo.tBill.AddTime ,
        dbo.tBill.MRemark ,
        dbo.tBill.FUser ,
        dbo.tBill.Maker ,
        dbo.tBill.MakerDate ,
        dbo.tBill.Editer ,
        dbo.tBill.EditDate ,
        dbo.tBill.BillType ,
        dbo.tBill.InOrOut ,
        dbo.tBill.VipCode ,
        dbo.tBill.PayType ,
        dbo.tBill.SkMoney ,
        dbo.tBill.ZLMoney ,
        dbo.tBill.YSMoney ,
        dbo.tBill.DepotName ,
        dbo.tBill.Field1 ,
        dbo.tBill.Field2 ,
        dbo.tBill.Field3 ,
        dbo.tBill.Field4 ,
        dbo.tBill.Field5 ,
        DATEPART(hh, dbo.tBill.AddTime) AS HJtime ,
        CAST(YEAR(dbo.tBill.AddDate) AS VARCHAR(4)) + '-'
        + CAST(MONTH(dbo.tBill.AddDate) AS VARCHAR(4)) AS HjMonth ,
        DATEPART(yyyy, dbo.tBill.AddDate) AS HjYear ,
        DATEPART(ww, dbo.tBill.AddDate) AS WeekDate ,
        '全部' AS AllBill ,
        dbo.tBillDetail.BillDetailID ,
        CAST(DATEPART(yyyy, dbo.tBill.AddDate) AS VARCHAR(4)) + '年'
        + CAST(DATEPART(q, dbo.tBill.AddDate) AS VARCHAR(4)) + '季度' AS HjSeason ,
        dbo.tVip.VipName ,
        dbo.tBillDetail.BarCode ,
        dbo.tBillDetail.DBillCode ,
        dbo.tBillDetail.GoodsStyle ,
        dbo.tBillDetail.GoodsName ,
        dbo.tBillDetail.Units ,
        dbo.tBillDetail.SizeGroup ,
        dbo.tBillDetail.ColorCode ,
        dbo.tBillDetail.ColorName ,
        dbo.tBillDetail.ColorValue ,
        dbo.tBillDetail.DetailField1 ,
        dbo.tBillDetail.DetailField2 ,
        dbo.tBillDetail.DetailField3 ,
        dbo.tBillDetail.DetailField4 ,
        dbo.tBillDetail.DetailField5 ,
        dbo.tBillDetail.SizeCode ,
        dbo.tBillDetail.SizeName ,
        dbo.tBillDetail.Number AS Number1 ,
        dbo.tBillDetail.Money AS Money1 ,
        dbo.tBillDetail.InMoney AS InMoney1 ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Number * tBillDetail.Sign * -1
               ELSE tBillDetail.Number * tBillDetail.Sign
          END ) AS Number ,
        dbo.tBillDetail.Discount ,
        dbo.tBillDetail.Price ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Money * tBillDetail.Sign * -1
               ELSE tBillDetail.Money * tBillDetail.Sign
          END ) AS Money ,
        dbo.tBillDetail.colPosition ,
        dbo.tBillDetail.rowPosition ,
        dbo.tBillDetail.DRemark ,
        dbo.tBillDetail.Sign ,
        dbo.tBillDetail.InPrice ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.InPrice * tBillDetail.Number
                    * tBillDetail.Sign * -1
               ELSE tBillDetail.InPrice * tBillDetail.Number
                    * tBillDetail.Sign
          END ) AS InMoney ,
        dbo.tGoods.dpPrice ,
        dbo.tGoods.xsPrice ,
        dbo.tGoods.GoodsSeason ,
        dbo.tGoods.DepotUp ,
        dbo.tGoods.DepotDown
FROM    dbo.tBill
        LEFT OUTER JOIN dbo.tBillDetail
        LEFT OUTER JOIN dbo.tGoods ON dbo.tBillDetail.GoodsStyle = dbo.tGoods.GoodsStyle ON dbo.tBill.BillCode = dbo.tBillDetail.DBillCode
        LEFT OUTER JOIN dbo.tVip ON dbo.tBill.VipCode = dbo.tVip.VipCode
UNION ALL
SELECT  tGoods_1.TypeCode ,
        tGoods_1.GoodsBrand ,
        tGoods_1.GoodsType ,
        tGoods_1.ifDisCount ,
        tGoods_1.Supply ,
        tGoods_1.Remark ,
        tBill_1.BillID ,
        tBill_1.DepotName ,
        tBill_1.BillCode ,
        tBill_1.AddDate ,
        tBill_1.AddTime ,
        tBill_1.MRemark ,
        tBill_1.FUser ,
        tBill_1.Maker ,
        tBill_1.MakerDate ,
        tBill_1.Editer ,
        tBill_1.EditDate ,
        tBill_1.BillType ,
        tBill_1.InOrOut ,
        tBill_1.VipCode ,
        tBill_1.PayType ,
        tBill_1.SkMoney ,
        tBill_1.ZLMoney ,
        tBill_1.YSMoney ,
        tBill_1.CustName ,
        tBill_1.Field1 ,
        tBill_1.Field2 ,
        tBill_1.Field3 ,
        tBill_1.Field4 ,
        tBill_1.Field5 ,
        DATEPART(hh, tBill_1.AddTime) AS HjTime ,
        CAST(YEAR(tBill_1.AddDate) AS VARCHAR(4)) + '-'
        + CAST(MONTH(tBill_1.AddDate) AS VARCHAR(4)) AS HjMonth ,
        DATEPART(yyyy, tBill_1.AddDate) AS HjYear ,
        DATEPART(ww, tBill_1.AddDate) AS WeekDate ,
        '全部' AS AllBill ,
        tBillDetail_1.BillDetailID ,
        CAST(DATEPART(yyyy, tBill_1.AddDate) AS VARCHAR(4)) + '年'
        + CAST(DATEPART(q, tBill_1.AddDate) AS VARCHAR(4)) + '季度' AS HjSeason ,
        tVip_1.VipName ,
        tBillDetail_1.BarCode ,
        tBillDetail_1.DBillCode ,
        tBillDetail_1.GoodsStyle ,
        tBillDetail_1.GoodsName ,
        tBillDetail_1.Units ,
        tBillDetail_1.SizeGroup ,
        tBillDetail_1.ColorCode ,
        tBillDetail_1.ColorName ,
        tBillDetail_1.ColorValue ,
        tBillDetail_1.DetailField1 ,
        tBillDetail_1.DetailField2 ,
        tBillDetail_1.DetailField3 ,
        tBillDetail_1.DetailField4 ,
        tBillDetail_1.DetailField5 ,
        tBillDetail_1.SizeCode ,
        tBillDetail_1.SizeName ,
        tBillDetail_1.Number AS Number1 ,
        tBillDetail_1.Money AS Money1 ,
        tBillDetail_1.InMoney AS InMoney1 ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Number * 1 * -1
               ELSE tBillDetail.Number * 1
          END ) AS Number ,
        tBillDetail_1.Discount ,
        tBillDetail_1.Price ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Money * 1 * -1
               ELSE tBillDetail.Money * 1
          END ) AS Money ,
        tBillDetail_1.colPosition ,
        tBillDetail_1.rowPosition ,
        tBillDetail_1.DRemark ,
        1 AS Expr1 ,
        tBillDetail_1.InPrice ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.InPrice * tBillDetail.Number * 1 * -1
               ELSE tBillDetail.InPrice * tBillDetail.Number * 1
          END ) AS InMoney ,
        tGoods_1.dpPrice ,
        tGoods_1.xsPrice ,
        tGoods_1.GoodsSeason ,
        tGoods_1.DepotUp ,
        tGoods_1.DepotDown
FROM    dbo.tBill AS tBill_1
        LEFT OUTER JOIN dbo.tBillDetail AS tBillDetail_1
        LEFT OUTER JOIN dbo.tGoods AS tGoods_1 ON tBillDetail_1.GoodsStyle = tGoods_1.GoodsStyle ON tBill_1.BillCode = tBillDetail_1.DBillCode
        LEFT OUTER JOIN dbo.tVip AS tVip_1 ON tBill_1.VipCode = tVip_1.VipCode
WHERE   ( tBill_1.BillType = '店铺调拨' )
LongRui888 2014-01-14
  • 打赏
  • 举报
 回复
用排除法。 先运行第一段，看看有没有问题： 
SELECT     dbo.tGoods.TypeCode, dbo.tGoods.GoodsBrand, dbo.tGoods.GoodsType, dbo.tGoods.ifDisCount, dbo.tGoods.Supply, dbo.tGoods.Remark, dbo.tBill.BillID, 
    dbo.tBill.CustName, dbo.tBill.BillCode, dbo.tBill.AddDate, dbo.tBill.AddTime, dbo.tBill.MRemark, dbo.tBill.FUser, dbo.tBill.Maker, dbo.tBill.MakerDate, dbo.tBill.Editer, 
    dbo.tBill.EditDate, dbo.tBill.BillType, dbo.tBill.InOrOut, dbo.tBill.VipCode, dbo.tBill.PayType, dbo.tBill.SkMoney, dbo.tBill.ZLMoney, dbo.tBill.YSMoney, 
    dbo.tBill.DepotName, dbo.tBill.Field1, dbo.tBill.Field2, dbo.tBill.Field3, dbo.tBill.Field4, dbo.tBill.Field5, DATEPART(hh, dbo.tBill.AddTime) AS HJtime, 
    CAST(YEAR(dbo.tBill.AddDate) AS varchar(4)) + '-' + CAST(MONTH(dbo.tBill.AddDate) AS varchar(4)) AS HjMonth, DATEPART(yyyy, dbo.tBill.AddDate) AS HjYear, 
    DATEPART(ww, dbo.tBill.AddDate) AS WeekDate, '全部' AS AllBill, dbo.tBillDetail.BillDetailID, CAST(DATEPART(yyyy, dbo.tBill.AddDate) AS varchar(4)) 
    + '年' + CAST(DATEPART(q, dbo.tBill.AddDate) AS varchar(4)) + '季度' AS HjSeason, dbo.tVip.VipName, dbo.tBillDetail.BarCode, dbo.tBillDetail.DBillCode, 
    dbo.tBillDetail.GoodsStyle, dbo.tBillDetail.GoodsName, dbo.tBillDetail.Units, dbo.tBillDetail.SizeGroup, dbo.tBillDetail.ColorCode, dbo.tBillDetail.ColorName, 
    dbo.tBillDetail.ColorValue, dbo.tBillDetail.DetailField1, dbo.tBillDetail.DetailField2, dbo.tBillDetail.DetailField3, dbo.tBillDetail.DetailField4, dbo.tBillDetail.DetailField5, 
    dbo.tBillDetail.SizeCode, dbo.tBillDetail.SizeName, dbo.tBillDetail.Number AS Number1, dbo.tBillDetail.Money AS Money1, dbo.tBillDetail.InMoney AS InMoney1, 
    (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') THEN tBillDetail.Number * tBillDetail.Sign * - 1 ELSE tBillDetail.Number * tBillDetail.Sign END) AS Number, 
    dbo.tBillDetail.Discount, dbo.tBillDetail.Price, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 
    THEN tBillDetail.Money * tBillDetail.Sign * - 1 ELSE tBillDetail.Money * tBillDetail.Sign END) AS Money, dbo.tBillDetail.colPosition, dbo.tBillDetail.rowPosition, 
    dbo.tBillDetail.DRemark, dbo.tBillDetail.Sign, dbo.tBillDetail.InPrice, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 
    THEN tBillDetail.InPrice * tBillDetail.Number * tBillDetail.Sign * - 1 ELSE tBillDetail.InPrice * tBillDetail.Number * tBillDetail.Sign END) AS InMoney, 
    dbo.tGoods.dpPrice, dbo.tGoods.xsPrice, dbo.tGoods.GoodsSeason, dbo.tGoods.DepotUp, dbo.tGoods.DepotDown
FROM         dbo.tBill LEFT OUTER JOIN
    dbo.tBillDetail LEFT OUTER JOIN
    dbo.tGoods ON dbo.tBillDetail.GoodsStyle = dbo.tGoods.GoodsStyle ON dbo.tBill.BillCode = dbo.tBillDetail.DBillCode LEFT OUTER JOIN
    dbo.tVip ON dbo.tBill.VipCode = dbo.tVip.VipCode
再运行第二段，看看有没有问题： 
SELECT     tGoods_1.TypeCode, tGoods_1.GoodsBrand, tGoods_1.GoodsType, tGoods_1.ifDisCount, tGoods_1.Supply, tGoods_1.Remark, tBill_1.BillID, tBill_1.DepotName, 
    tBill_1.BillCode, tBill_1.AddDate, tBill_1.AddTime, tBill_1.MRemark, tBill_1.FUser, tBill_1.Maker, tBill_1.MakerDate, tBill_1.Editer, tBill_1.EditDate, tBill_1.BillType, 
    tBill_1.InOrOut, tBill_1.VipCode, tBill_1.PayType, tBill_1.SkMoney, tBill_1.ZLMoney, tBill_1.YSMoney, tBill_1.CustName, tBill_1.Field1, tBill_1.Field2, tBill_1.Field3, 
    tBill_1.Field4, tBill_1.Field5, DATEPART(hh, tBill_1.AddTime) AS HjTime, CAST(YEAR(tBill_1.AddDate) AS varchar(4)) + '-' + CAST(MONTH(tBill_1.AddDate) AS varchar(4))
     AS HjMonth, DATEPART(yyyy, tBill_1.AddDate) AS HjYear, DATEPART(ww, tBill_1.AddDate) AS WeekDate, '全部' AS AllBill, tBillDetail_1.BillDetailID, 
    CAST(DATEPART(yyyy, tBill_1.AddDate) AS varchar(4)) + '年' + CAST(DATEPART(q, tBill_1.AddDate) AS varchar(4)) + '季度' AS HjSeason, tVip_1.VipName, 
    tBillDetail_1.BarCode, tBillDetail_1.DBillCode, tBillDetail_1.GoodsStyle, tBillDetail_1.GoodsName, tBillDetail_1.Units, tBillDetail_1.SizeGroup, 
    tBillDetail_1.ColorCode, tBillDetail_1.ColorName, tBillDetail_1.ColorValue, tBillDetail_1.DetailField1, tBillDetail_1.DetailField2, tBillDetail_1.DetailField3, 
    tBillDetail_1.DetailField4, tBillDetail_1.DetailField5, tBillDetail_1.SizeCode, tBillDetail_1.SizeName, tBillDetail_1.Number AS Number1, 
    tBillDetail_1.Money AS Money1, tBillDetail_1.InMoney AS InMoney1, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 
    THEN tBillDetail.Number * 1 * - 1 ELSE tBillDetail.Number * 1 END) AS Number, tBillDetail_1.Discount, tBillDetail_1.Price, (CASE WHEN tBill.BillType IN ('销售出货', 
    '销售退货') THEN tBillDetail.Money * 1 * - 1 ELSE tBillDetail.Money * 1 END) AS Money, tBillDetail_1.colPosition, tBillDetail_1.rowPosition, tBillDetail_1.DRemark, 
    1 AS Expr1, tBillDetail_1.InPrice, (CASE WHEN tBill.BillType IN ('销售出货', '销售退货') 
    THEN tBillDetail.InPrice * tBillDetail.Number * 1 * - 1 ELSE tBillDetail.InPrice * tBillDetail.Number * 1 END) AS InMoney, tGoods_1.dpPrice, tGoods_1.xsPrice, 
    tGoods_1.GoodsSeason, tGoods_1.DepotUp, tGoods_1.DepotDown
FROM         dbo.tBill AS tBill_1 LEFT OUTER JOIN
    dbo.tBillDetail AS tBillDetail_1 LEFT OUTER JOIN
    dbo.tGoods AS tGoods_1 ON tBillDetail_1.GoodsStyle = tGoods_1.GoodsStyle ON tBill_1.BillCode = tBillDetail_1.DBillCode LEFT OUTER JOIN
    dbo.tVip AS tVip_1 ON tBill_1.VipCode = tVip_1.VipCode
WHERE     (tBill_1.BillType = '店铺调拨')
大张 2014-01-14
  • 打赏
  • 举报
 回复
好的，我试试，谢谢，我是一点头绪没有
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
这种体力活，没有环境很累人，你把union all的下部分注释掉，看看是不是上面一部分出问题，如果不是，反过来看看是不是下一部分出问题，逐层分解
大张 2014-01-14
  • 打赏
  • 举报
 回复
大张 2014-01-14
  • 打赏
  • 举报
 回复
不好意思，刚才看漏了一位，还有yupeigu老师，还要谢谢您
大张 2014-01-14
  • 打赏
  • 举报
 回复
好了，谢谢DBA_Huangzj 版主， 这个问题困扰我好几天了，今天终于遇到了高人！
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
你如果不想改那么多SELECT的字段，就取消FROM里面的别名
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
引用 8 楼 hongendazhang 的回复:
是不是 dbo.tBill AS tBill_1 LEFT OUTER JOIN dbo.tBillDetail AS tBillDetail_1 这里的原因，这里被 命名别名了。 而这里
 ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Number * 1 * -1
               ELSE tBillDetail.Number * 1
          END ) AS Number ,
        tBillDetail_1.Discount ,
        tBillDetail_1.Price ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Money * 1 * -1
               ELSE tBillDetail.Money * 1
用的tBill，我试试是不是这里的问题！
是的，因为用了别名，所以SELECT那里要用别名的名字
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
找到你的问题了，你的FROM dbo.tBill AS tBill_1 这里别名用了tbill_1，所以select中的tbill.billtype这类的要换成tbill_1.billtype
大张 2014-01-14
  • 打赏
  • 举报
 回复
是不是 dbo.tBill AS tBill_1 LEFT OUTER JOIN dbo.tBillDetail AS tBillDetail_1 这里的原因，这里被 命名别名了。 而这里
 ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Number * 1 * -1
               ELSE tBillDetail.Number * 1
          END ) AS Number ,
        tBillDetail_1.Discount ,
        tBillDetail_1.Price ,
        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )
               THEN tBillDetail.Money * 1 * -1
               ELSE tBillDetail.Money * 1
用的tBill，我试试是不是这里的问题！
發糞塗牆 2014-01-14
  • 打赏
  • 举报
 回复
大张 2014-01-14
  • 打赏
  • 举报
 回复
谢谢版主，发现报错在第二段，不过没找到 您说的勾
SELECT  tGoods_1.TypeCode ,

        tGoods_1.GoodsBrand ,

        tGoods_1.GoodsType ,

        tGoods_1.ifDisCount ,

        tGoods_1.Supply ,

        tGoods_1.Remark ,

        tBill_1.BillID ,

        tBill_1.DepotName ,

        tBill_1.BillCode ,

        tBill_1.AddDate ,

        tBill_1.AddTime ,

        tBill_1.MRemark ,

        tBill_1.FUser ,

        tBill_1.Maker ,

        tBill_1.MakerDate ,

        tBill_1.Editer ,

        tBill_1.EditDate ,

        tBill_1.BillType ,

        tBill_1.InOrOut ,

        tBill_1.VipCode ,

        tBill_1.PayType ,

        tBill_1.SkMoney ,

        tBill_1.ZLMoney ,

        tBill_1.YSMoney ,

        tBill_1.CustName ,

        tBill_1.Field1 ,

        tBill_1.Field2 ,

        tBill_1.Field3 ,

        tBill_1.Field4 ,

        tBill_1.Field5 ,

        DATEPART(hh, tBill_1.AddTime) AS HjTime ,

        CAST(YEAR(tBill_1.AddDate) AS VARCHAR(4)) + '-'

        + CAST(MONTH(tBill_1.AddDate) AS VARCHAR(4)) AS HjMonth ,

        DATEPART(yyyy, tBill_1.AddDate) AS HjYear ,

        DATEPART(ww, tBill_1.AddDate) AS WeekDate ,

        '全部' AS AllBill ,

        tBillDetail_1.BillDetailID ,

        CAST(DATEPART(yyyy, tBill_1.AddDate) AS VARCHAR(4)) + '年'

        + CAST(DATEPART(q, tBill_1.AddDate) AS VARCHAR(4)) + '季度' AS HjSeason ,

        tVip_1.VipName ,

        tBillDetail_1.BarCode ,

        tBillDetail_1.DBillCode ,

        tBillDetail_1.GoodsStyle ,

        tBillDetail_1.GoodsName ,

        tBillDetail_1.Units ,

        tBillDetail_1.SizeGroup ,

        tBillDetail_1.ColorCode ,

        tBillDetail_1.ColorName ,

        tBillDetail_1.ColorValue ,

        tBillDetail_1.DetailField1 ,

        tBillDetail_1.DetailField2 ,

        tBillDetail_1.DetailField3 ,

        tBillDetail_1.DetailField4 ,

        tBillDetail_1.DetailField5 ,

        tBillDetail_1.SizeCode ,

        tBillDetail_1.SizeName ,

        tBillDetail_1.Number AS Number1 ,

        tBillDetail_1.Money AS Money1 ,

        tBillDetail_1.InMoney AS InMoney1 ,

        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )

               THEN tBillDetail.Number * 1 * -1

               ELSE tBillDetail.Number * 1

          END ) AS Number ,

        tBillDetail_1.Discount ,

        tBillDetail_1.Price ,

        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )

               THEN tBillDetail.Money * 1 * -1

               ELSE tBillDetail.Money * 1

          END ) AS Money ,

        tBillDetail_1.colPosition ,

        tBillDetail_1.rowPosition ,

        tBillDetail_1.DRemark ,

        1 AS Expr1 ,

        tBillDetail_1.InPrice ,

        ( CASE WHEN tBill.BillType IN ( '销售出货', '销售退货' )

               THEN tBillDetail.InPrice * tBillDetail.Number * 1 * -1

               ELSE tBillDetail.InPrice * tBillDetail.Number * 1

          END ) AS InMoney ,

        tGoods_1.dpPrice ,

        tGoods_1.xsPrice ,

        tGoods_1.GoodsSeason ,

        tGoods_1.DepotUp ,

        tGoods_1.DepotDown

FROM    dbo.tBill AS tBill_1

        LEFT OUTER JOIN dbo.tBillDetail AS tBillDetail_1

        LEFT OUTER JOIN dbo.tGoods AS tGoods_1 ON tBillDetail_1.GoodsStyle = tGoods_1.GoodsStyle ON tBill_1.BillCode = tBillDetail_1.DBillCode

        LEFT OUTER JOIN dbo.tVip AS tVip_1 ON tBill_1.VipCode = tVip_1.VipCode

WHERE   ( tBill_1.BillType = '店铺调拨' )

将Access换成sql要改些什么?注意哪些问题?（汇总）
为什么要改MSSQL是因为MSSQL比Access储存有更多的优势 最被大家所认知的就是存储过程了 所以access版的程序改mssql的话 那首先应该将最常用的，不可变的sql语句转化为存储过程 又因为mssql支持参数化的sql查询，而access也支持参数化的sql查询 在转移到mssql平台后，所有sql语句中的参数对象占位符也要由access下的"?"改为mssql下
access 和SQL有什么不同
Access中提供查询对象，在设计时可以采用设计视图和SQL视图，非常方便，SQL视图中的SQL语句可以在SQL SERVER中使用，但是否完全可用呢？答案是否定的，表1中总结了 Microsoft Access 和 Microsoft SQL Servers 的 SQL 语法的重要区别。 web配置 help帮助类 private static readonly string Connec
关于MS SQL导出到Access的一个问题
常用Access 数据类型与 MS SQL 数据类型的相应：文本 nvarchar(n)备注 ntext数字(长整型) int数字(整型) smallint数字(单精度) real数字(双精度) float数字(字节) tinyint货币 money日期 smalldatetime布尔 bitAccess允许九种数据类型：http://publishblog.blogchina.com/blog/
《转》Vmware vSphere常见问题汇总
1、启用客户机操作系统和远程控制台之间的复制和粘贴操作 解决方法：要在客户机操作系统和远程控制台之间进行复制和粘贴，必须使用 vSphere Client 启用复制和粘贴操作。 步骤 a、使用 vSphere Client 登录到 vCenter Server 系统并选择虚拟机。 b、在摘要选项卡中，单击编辑设置。 c、选择选项 > 高级 > 常规，然后单击配置参
mssql系统存储过程
xp_cmdshell --*执行DOS各种命令，结果以文本行返回。xp_fixeddrives --*查询各磁盘/分区可用空间xp_loginconfig --*报告SQL Server 实例在Windows 上运行时的登录安全配置xp_logininfo --*返回有关Windows 认证登录的信息。xp_msver --*返回有关Microsoft SQL S...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章