视图的编写解决办法

视图的编写
麻烦帮忙看一下以下语句，哪里出错，我应该如何修改？

SQL code

SELECT  distinct                        dbo.Customer.cCusAbbName AS 客户简称, dbo.SO_SOMain.cSOCode AS 订单号,dbo.SO_SOMain.dDate AS 订单日期,                       dbo.Person.cPersonName AS 业务员, dbo.Inventory.cInvCode AS 存货编码,dbo.Inventory.cInvName AS 存货名称,                        dbo.Inventory.cInvStd AS 规格型号,dbo.SO_SODetails.iQuantity AS 数量,dbo.ComputationUnit.cComUnitName AS 主计量,                       dbo.SO_SODetails.iTaxUnitPrice AS 含税单价,dbo.SO_SODetails.iSum AS 价税合计,                      ISNULL(dbo.SO_SODetails.foutquantity, 0) AS 出库数量,                       (case when isnull(sum(distinct RdRecords.iQuantity),0)=0 then isnull(sum(distinct RdRecords.iQuantity),0)+ISNULL(dbo.SO_SODetails.foutquantity, 0) else isnull(sum(distinct RdRecords.iQuantity),0) end) as 累计入库数量,                      MAX(dbo.RdRecord.dDate) AS 入库最后日期,dbo.SO_SODetails.dPreDate AS 预发货日期, MAX(dbo.DispatchList.dDate) AS 最后发货日期,                      dbo.SO_SODetails.iFHQuantity AS 累计发货数量,dbo.SO_SOMain.cCusOAddress AS 发货地址, dbo.SO_SODetails.fretquantity AS 累计退货数量,                       dbo.SO_SODetails.iKPQuantity AS 累计开票数量,dbo.SO_SODetails.iKPMoney AS 累计开票金额,                      ISNULL(dbo.SO_SODetails.imoneysum, 0) AS 累计收款,ISNULL(dbo.SO_SODetails.iexchsum, 0) AS 累计收款原币,                      dbo.SO_SOMain.cMaker AS 制单人,dbo.SO_SOMain.cVerifier AS 审核人,dbo.SO_SOMain.cCloser AS 关闭人,                       dbo.SO_SODetails.cSCloser AS 行关闭人FROM                  dbo.SO_SODetails right outer join            dbo.Inventory on dbo.Inventory.cInvCode = dbo.SO_SODetails.cInvCode left outer join           dbo.ComputationUnit on dbo.Inventory.cComUnitCode = dbo.ComputationUnit.cComunitCode on dbo.SO_SODetails.cUnitID = ComputationUnit.cComunitCode            right outer join dbo.so_somain on dbo.so_somain.csocode=dbo.so_sodetails.csocode           left outer join dbo.Person ON dbo.Person.cPersonCode=dbo.SO_SOMain.cPersonCode on dbo.SO_SODetails.ID = dbo.SO_SOMain.ID           right outer join dbo.Customer on dbo.Customer.cCusCode = dbo.SO_SOMain.cCusCode           left outer join dbo.RdRecord ON dbo.SO_SODetails.cSOCode = dbo.RdRecord.cDefine10            RIGHT OUTER JOIN dbo.RdRecords ON dbo.RdRecord.ID = dbo.RdRecords.ID AND dbo.SO_SODetails.cInvCode = dbo.RdRecords.cInvCode on dbo.SO_SOMain.cSOCode = dbo.RdRecord.cDefine10            LEFT OUTER JOIN dbo.DispatchList ON dbo.DispatchList.cSOCode = dbo.RdRecord.cDefine10                              group by dbo.Customer.cCusAbbName,dbo.SO_SOMain.cSOCode,dbo.SO_SOMain.dDate,dbo.Person.cPersonName,dbo.Inventory.cInvCode,         dbo.Inventory.cInvName,dbo.Inventory.cInvStd,dbo.SO_SODetails.iQuantity,dbo.ComputationUnit.cComUnitName,dbo.SO_SODetails.iTaxUnitPrice,         dbo.SO_SODetails.iSum,dbo.SO_SODetails.foutquantity,dbo.SO_SODetails.dPreDate,dbo.SO_SODetails.iFHQuantity,dbo.SO_SOMain.cCusOAddress,         dbo.SO_SODetails.fretquantity,dbo.SO_SODetails.iKPQuantity,dbo.SO_SODetails.iKPMoney,dbo.SO_SODetails.imoneysum,dbo.SO_SODetails.iexchsum,         dbo.SO_SOMain.cMaker,dbo.SO_SOMain.cVerifier,dbo.SO_SOMain.cCloser,dbo.SO_SODetails.cSCloser

报错：消息 156，级别 15，状态 1，第 19 行
关键字 'on' 附近有语法错误。

表关系太多看的我眼花，我是想把所有我需要的数据以视图的方式查出来，然后将2009年数据库中相同视图的内容 和 2010年数据库中相同视图的内容 放在一张表中（2010年库中有2009年库中年结过来的数据，为了避免发生重复数据，在2010年库中进行视图编写时 加一个条件：最后入库日期 between '2010-01-01' and '2010-12-31'）,但是两个库中的数据放在一张表之后，2010年库中的数据还会因为客户的添加有所变化，怎样能让它自动添加客户新加的数据，排除 使用触发器完成。
原因：客户所用的软件是已经封装好的成品软件，我只能使用查询的方法，不能以改变客户目前所用的任何操作，以免操作中出现其它误差，难以弥补。
还希望各位高手能伸出援助之手，谢谢！

[解决办法]
left outer join dbo.Person ON dbo.Person.cPersonCode=dbo.SO_SOMain.cPersonCode on dbo.SO_SODetails.ID = dbo

把红色的ON改成and
[解决办法]

SQL code

FROM                  dbo.SO_SODetails right outer join            dbo.Inventory on dbo.Inventory.cInvCode = dbo.SO_SODetails.cInvCode left outer join           dbo.ComputationUnit on dbo.Inventory.cComUnitCode = dbo.ComputationUnit.cComunitCode [color=#00FFFF]and [/color]dbo.SO_SODetails.cUnitID = ComputationUnit.cComunitCode            right outer join dbo.so_somain on dbo.so_somain.csocode=dbo.so_sodetails.csocode           left outer join dbo.Person ON dbo.Person.cPersonCode=dbo.SO_SOMain.cPersonCode [color=#00FF00]and [/color]dbo.SO_SODetails.ID = dbo.SO_SOMain.ID           right outer join dbo.Customer on dbo.Customer.cCusCode = dbo.SO_SOMain.cCusCode           left outer join dbo.RdRecord ON dbo.SO_SODetails.cSOCode = dbo.RdRecord.cDefine10            RIGHT OUTER JOIN dbo.RdRecords ON dbo.RdRecord.ID = dbo.RdRecords.ID AND dbo.SO_SODetails.cInvCode = dbo.RdRecords.cInvCode [color=#00FFFF]and [/color]dbo.SO_SOMain.cSOCode = dbo.RdRecord.cDefine10            LEFT OUTER JOIN dbo.DispatchList ON dbo.DispatchList.cSOCode = dbo.RdRecord.cDefine10试试这样还报错不？
[解决办法]
连接两个表的多条件用AND /OR 等其他连接语法，不是ON，
[解决办法]
有三个on需要改成and
SQL code
SELECT  DISTINCT        dbo.Customer.cCusAbbName AS 客户简称 ,        dbo.SO_SOMain.cSOCode AS 订单号 ,        dbo.SO_SOMain.dDate AS 订单日期 ,        dbo.Person.cPersonName AS 业务员 ,        dbo.Inventory.cInvCode AS 存货编码 ,        dbo.Inventory.cInvName AS 存货名称 ,        dbo.Inventory.cInvStd AS 规格型号 ,        dbo.SO_SODetails.iQuantity AS 数量 ,        dbo.ComputationUnit.cComUnitName AS 主计量 ,        dbo.SO_SODetails.iTaxUnitPrice AS 含税单价 ,        dbo.SO_SODetails.iSum AS 价税合计 ,        ISNULL(dbo.SO_SODetails.foutquantity, 0) AS 出库数量 ,        ( CASE WHEN ISNULL(SUM(DISTINCT RdRecords.iQuantity), 0) = 0               THEN ISNULL(SUM(DISTINCT RdRecords.iQuantity), 0)                    + ISNULL(dbo.SO_SODetails.foutquantity, 0)               ELSE ISNULL(SUM(DISTINCT RdRecords.iQuantity), 0)          END ) AS 累计入库数量 ,        MAX(dbo.RdRecord.dDate) AS 入库最后日期 ,        dbo.SO_SODetails.dPreDate AS 预发货日期 ,        MAX(dbo.DispatchList.dDate) AS 最后发货日期 ,        dbo.SO_SODetails.iFHQuantity AS 累计发货数量 ,        dbo.SO_SOMain.cCusOAddress AS 发货地址 ,        dbo.SO_SODetails.fretquantity AS 累计退货数量 ,        dbo.SO_SODetails.iKPQuantity AS 累计开票数量 ,        dbo.SO_SODetails.iKPMoney AS 累计开票金额 ,        ISNULL(dbo.SO_SODetails.imoneysum, 0) AS 累计收款 ,        ISNULL(dbo.SO_SODetails.iexchsum, 0) AS 累计收款原币 ,        dbo.SO_SOMain.cMaker AS 制单人 ,        dbo.SO_SOMain.cVerifier AS 审核人 ,        dbo.SO_SOMain.cCloser AS 关闭人 ,        dbo.SO_SODetails.cSCloser AS 行关闭人FROM    dbo.SO_SODetails        RIGHT OUTER JOIN dbo.Inventory ON dbo.Inventory.cInvCode = dbo.SO_SODetails.cInvCode        LEFT OUTER JOIN dbo.ComputationUnit ON dbo.Inventory.cComUnitCode = dbo.ComputationUnit.cComunitCode                                               AND dbo.SO_SODetails.cUnitID = ComputationUnit.cComunitCode        RIGHT OUTER JOIN dbo.so_somain ON dbo.so_somain.csocode = dbo.so_sodetails.csocode        LEFT OUTER JOIN dbo.Person ON dbo.Person.cPersonCode = dbo.SO_SOMain.cPersonCode                                      AND dbo.SO_SODetails.ID = dbo.SO_SOMain.ID        RIGHT OUTER JOIN dbo.Customer ON dbo.Customer.cCusCode = dbo.SO_SOMain.cCusCode        LEFT OUTER JOIN dbo.RdRecord ON dbo.SO_SODetails.cSOCode = dbo.RdRecord.cDefine10        RIGHT OUTER JOIN dbo.RdRecords ON dbo.RdRecord.ID = dbo.RdRecords.ID                                          AND dbo.SO_SODetails.cInvCode = dbo.RdRecords.cInvCode                                          AND dbo.SO_SOMain.cSOCode = dbo.RdRecord.cDefine10        LEFT OUTER JOIN dbo.DispatchList ON dbo.DispatchList.cSOCode = dbo.RdRecord.cDefine10GROUP BY dbo.Customer.cCusAbbName ,        dbo.SO_SOMain.cSOCode ,        dbo.SO_SOMain.dDate ,        dbo.Person.cPersonName ,        dbo.Inventory.cInvCode ,        dbo.Inventory.cInvName ,        dbo.Inventory.cInvStd ,        dbo.SO_SODetails.iQuantity ,        dbo.ComputationUnit.cComUnitName ,        dbo.SO_SODetails.iTaxUnitPrice ,        dbo.SO_SODetails.iSum ,        dbo.SO_SODetails.foutquantity ,        dbo.SO_SODetails.dPreDate ,        dbo.SO_SODetails.iFHQuantity ,        dbo.SO_SOMain.cCusOAddress ,        dbo.SO_SODetails.fretquantity ,        dbo.SO_SODetails.iKPQuantity ,        dbo.SO_SODetails.iKPMoney ,        dbo.SO_SODetails.imoneysum ,        dbo.SO_SODetails.iexchsum ,        dbo.SO_SOMain.cMaker ,        dbo.SO_SOMain.cVerifier ,        dbo.SO_SOMain.cCloser ,        dbo.SO_SODetails.cSCloser 
 
[解决办法]
探讨

有三个on需要改成and

SQL code


SELECT  DISTINCT
        dbo.Customer.cCusAbbName AS 客户简称 ,
        dbo.SO_SOMain.cSOCode AS 订单号 ,
        dbo.SO_SOMain.dDate AS 订单日期 ,
        dbo.Person.cPersonNam……
 

 

查看更多 下一篇