[求助]最近专攻MSSQL,这条语句好有难度！ - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Create view [dbo].[vBillDetailMx] As select tgoods.typecode, tgoods.goodsbrand, tgoods.goodstype, tgoods.ifdiscount, tgoods.supply, tgoods.remark, billid, custname, billcode, tbill.adddate, addtime, mremark, fuser, maker, makerdate, editer, editdate, billtype, inorout, tbill.vipcode, paytype, skmoney, zlmoney, ysmoney, depotname, field1, field2, field3, field4, field5, DatePart(hh,tbill.addtime) as hjtime, (Year(tbill.adddate) + '-' + Month(tbill.adddate)) as hjmonth, DatePart(yyyy,tbill.adddate) as hjyear, DatePart(ww,tbill.adddate) as weekdate, '全部' as allbill, tbilldetail.billdetailid, (Case DatePart(q,tbill.adddate) When 1 Then DatePart(yyyy,tbill.adddate) +'年一季度' When 2 Then DatePart(yyyy,tbill.adddate)+'年二季度' When 3 Then DatePart(yyyy,tbill.adddate)+'年三季度' When 4 Then DatePart(yyyy,tbill.adddate)+'年四季度') End) as hjseason, tvip.vipname, tbilldetail.barcode, tbilldetail.dbillcode, tbilldetail.goodsstyle, tbilldetail.goodsname, tbilldetail.units, tbilldetail.sizegroup, tbilldetail.colorcode, tbilldetail.colorname, tbilldetail.colorvalue, tbilldetail.detailfield1, tbilldetail.detailfield2, tbilldetail.detailfield3, tbilldetail.detailfield4, tbilldetail.detailfield5, tbilldetail.sizecode, tbilldetail.sizename, tbilldetail.number as number1, tbilldetail.money as money1, tbilldetail.inmoney as inmoney1, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.number*tbilldetail.sign*-1 Else tbilldetail.number*tbilldetail.sign End) as [number], tbilldetail.discount, tbilldetail.price, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.money*tbilldetail.sign*-1 Else tbilldetail.money*tbilldetail.sign End) as [money], tbilldetail.colposition, tbilldetail.rowposition, tbilldetail.dremark, tbilldetail.sign, tbilldetail.inprice, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.inprice*tbilldetail.number*tbilldetail.sign*-1 Else tbilldetail.inprice*tbilldetail.number*tbilldetail.sign End) as inmoney, tgoods.dpprice, tgoods.xsprice, tgoods.goodsseason, tgoods.depotup, tgoods.depotdown from (tbill left join (tbilldetail left join tgoods on tbilldetail.goodsstyle=tgoods.goodsstyle) on tbill.billcode=tbilldetail.dbillcode) left join tvip on tbill.vipcode=tvip.vipcode

消息 102，级别 15，状态 1，过程 vBillDetailMx，第 1 行

')' 附近有语法错误。

给你个建议，你可以把这个语句分成几个过渡表，再通过这些表合成该语句，这样语句就很简单

这个想单纯凭肉眼看出错误来,而且不是自己写的,难度有点高,费时费力,成本比重新写一遍还高图片点击可在新窗口打开查看

这是个创建视图的语句，咋能写成过渡表图片点击可在新窗口打开查看

他的意思是,建立几个视图, 然后再组合这几个视图,创建新的试图,语句就会简洁一点,容易找到问题,和逻辑.

有装MSSQL的大能用查询分析器给试试吧，小弟都研究三天了还没成功。

这原本是个access里的查询，现在想转成MSSQL.

这是原语句

Create view [dbo].[vBillDetailMx] As select tgoods.typecode, tgoods.goodsbrand, tgoods.goodstype, tgoods.ifdiscount, tgoods.supply, tgoods.remark, billid, custname, billcode, tbill.adddate, addtime, mremark, fuser, maker, makerdate, editer, editdate, billtype, inorout, tbill.vipcode, paytype, skmoney, zlmoney, ysmoney, depotname, field1, field2, field3, field4, field5, format(tbill.addtime,'hh') as hjtime, format(tbill.adddate,'yyyy-mm') as hjmonth, format(tbill.adddate,'yyyy') as hjyear, format(tbill.adddate,'aaaa') as weekdate, '全部' as allbill, tbilldetail.billdetailid, iif(month(tbill.adddate) in (1,2,3),format(tbill.adddate,'yyyy')+'年一季度',iif(month(tbill.adddate) in (4,5,6),format(tbill.adddate,'yyyy')+'年二季度',iif(month(tbill.adddate) in (7,8,9),format(tbill.adddate,'yyyy')+'年三季度',iif(month(tbill.adddate) in (10,11,12),format(tbill.adddate,'yyyy')+'年四季度')))) as hjseason, tvip.vipname, tbilldetail.barcode, tbilldetail.dbillcode, tbilldetail.goodsstyle, tbilldetail.goodsname, tbilldetail.units, tbilldetail.sizegroup, tbilldetail.colorcode, tbilldetail.colorname, tbilldetail.colorvalue, tbilldetail.detailfield1, tbilldetail.detailfield2, tbilldetail.detailfield3, tbilldetail.detailfield4, tbilldetail.detailfield5, tbilldetail.sizecode, tbilldetail.sizename, tbilldetail.number as number1, tbilldetail.money as money1, tbilldetail.inmoney as inmoney1, iif(tbill.billtype in ('销售出货','销售退货'),tbilldetail.number*tbilldetail.sign*-1,tbilldetail.number*tbilldetail.sign) as [number], tbilldetail.discount, tbilldetail.price, iif(tbill.billtype in ('销售出货','销售退货'),tbilldetail.money*tbilldetail.sign*-1,tbilldetail.money*tbilldetail.sign) as [money], tbilldetail.colposition, tbilldetail.rowposition, tbilldetail.dremark, tbilldetail.sign, tbilldetail.inprice, iif(tbill.billtype in ('销售出货','销售退货'),tbilldetail.inprice*tbilldetail.number*tbilldetail.sign*-1,tbilldetail.inprice*tbilldetail.number*tbilldetail.sign) as inmoney, tgoods.dpprice, tgoods.xsprice, tgoods.goodsseason, tgoods.depotup, tgoods.depotdown from (tbill left join (tbilldetail left join tgoods on tbilldetail.goodsstyle=tgoods.goodsstyle) on tbill.billcode=tbilldetail.dbillcode) left join tvip on tbill.vipcode=tvip.vipcode

没有数据源是试不了的. jspta 的建议你可以参考一下.

以下是引用jnletao在2014-1-8 10:27:00的发言：
这是个创建视图的语句，咋能写成过渡表图片点击可在新窗口打开查看

你还不如直接用SSMS的视图先创建好几个视图，再拼接在一起，你很多语句看起来重复的，通过一个过渡视图，可以减少很多语句。

以下是引用jnletao在2014-1-8 10:21:00的发言：
Create view [dbo].[vBillDetailMx] As select tgoods.typecode, tgoods.goodsbrand, tgoods.goodstype, tgoods.ifdiscount, tgoods.supply, tgoods.remark, billid, custname, billcode, tbill.adddate, addtime, mremark, fuser, maker, makerdate, editer, editdate, billtype, inorout, tbill.vipcode, paytype, skmoney, zlmoney, ysmoney, depotname, field1, field2, field3, field4, field5, DatePart(hh,tbill.addtime) as hjtime, (Year(tbill.adddate) + '-' + Month(tbill.adddate)) as hjmonth, DatePart(yyyy,tbill.adddate) as hjyear, DatePart(ww,tbill.adddate) as weekdate, '全部' as allbill, tbilldetail.billdetailid, (Case DatePart(q,tbill.adddate) When 1 Then DatePart(yyyy,tbill.adddate) +'年一季度' When 2 Then DatePart(yyyy,tbill.adddate)+'年二季度' When 3 Then DatePart(yyyy,tbill.adddate)+'年三季度' When 4 Then DatePart(yyyy,tbill.adddate)+'年四季度') End) as hjseason, tvip.vipname, tbilldetail.barcode, tbilldetail.dbillcode, tbilldetail.goodsstyle, tbilldetail.goodsname, tbilldetail.units, tbilldetail.sizegroup, tbilldetail.colorcode, tbilldetail.colorname, tbilldetail.colorvalue, tbilldetail.detailfield1, tbilldetail.detailfield2, tbilldetail.detailfield3, tbilldetail.detailfield4, tbilldetail.detailfield5, tbilldetail.sizecode, tbilldetail.sizename, tbilldetail.number as number1, tbilldetail.money as money1, tbilldetail.inmoney as inmoney1, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.number*tbilldetail.sign*-1 Else tbilldetail.number*tbilldetail.sign End) as [number], tbilldetail.discount, tbilldetail.price, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.money*tbilldetail.sign*-1 Else tbilldetail.money*tbilldetail.sign End) as [money], tbilldetail.colposition, tbilldetail.rowposition, tbilldetail.dremark, tbilldetail.sign, tbilldetail.inprice, (Case tbill.billtype When In ('销售出货','销售退货') Then tbilldetail.inprice*tbilldetail.number*tbilldetail.sign*-1 Else tbilldetail.inprice*tbilldetail.number*tbilldetail.sign End) as inmoney, tgoods.dpprice, tgoods.xsprice, tgoods.goodsseason, tgoods.depotup, tgoods.depotdown from (tbill left join (tbilldetail left join tgoods on tbilldetail.goodsstyle=tgoods.goodsstyle) on tbill.billcode=tbilldetail.dbillcode) left join tvip on tbill.vipcode=tvip.vipcode

主题：[求助]最近专攻MSSQL,这条语句好有难度！