- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- [求助]最近专攻MSSQL,这条语句好有难度！ (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=44750)
-- 发布时间：2014/1/8 10:21:00
-- [求助]最近专攻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, 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
-- 发布时间：2014/1/8 10:21:00
--
消息 102，级别 15，状态 1，过程 vBillDetailMx，第 1 行
\')\' 附近有语法错误。
-- 发布时间：2014/1/8 10:23:00
--
给你个建议，你可以把这个语句分成几个过渡表，再通过这些表合成该语句，这样语句就很简单
-- 发布时间：2014/1/8 10:26:00
--
这个想单纯凭肉眼看出错误来,而且不是自己写的,难度有点高,费时费力,成本比重新写一遍还高
-- 发布时间：2014/1/8 10:27:00
--
这是个创建视图的 语句，咋能写成过渡表
-- 发布时间：2014/1/8 10:28:00
--
他的意思是,建立几个视图, 然后再组合这几个视图,创建新的试图,语句就会简洁一点,容易找到问题,和逻辑.
-- 发布时间：2014/1/8 10:30:00
--
有装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
-- 发布时间：2014/1/8 10:31:00
--
没有数据源是试不了的. jspta 的建议你可以参考一下.
-- 发布时间：2014/1/8 10:31:00
--
以下是引用jnletao在2014-1-8 10:27:00的发言：
这是个创建视图的 语句，咋能写成过渡表
这是个创建视图的 语句，咋能写成过渡表
你还不如直接用SSMS的视图先创建好几个视图，再拼接在一起，你很多语句看起来重复的，通过一个过渡视图，可以减少很多语句。
-- 发布时间：2014/1/8 11:02:00
--
以下是引用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
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