专家坐堂-SQL语句问题疑问Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  SQL语句问题疑问  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=3829)

--  作者：migold
--  发布时间：2009/8/7 9:36:00
--  SQL语句问题疑问

Dim Sum3 As string = "select Prod_ID," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS \'Prod_Number\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then 0 else isnull(DisMoney,0) end) AS \'DisMoney1\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(DisMoney,0) else 0 end) AS \'DisMoney2\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS \'DisMoney\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then 0 else isnull(CostMoney,0) end) AS \'CostMoney1\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(CostMoney,0) else 0 end) AS \'CostMoney2\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS \'CostMoney\'," & Chr(13) & Chr(10)
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) ProfitMoney," & Chr(13) & Chr(10)
Sum3 = Sum3 + "\'零售\'AS \'type\',\'最近1个月\' AS \'month\'" & Chr(13) & Chr(10)
Sum3 = Sum3 + "from ListSale A" & Chr(13) & Chr(10)
Sum3 = Sum3 + "left join (select autoid,unit_id,billtype,billdate,s_Syb from masterbill) b on a.bill_id=b.autoid" & Chr(13) & Chr(10)
Sum3 = Sum3 + "left join (select s_id from units) c on b.unit_id=c.s_id" & Chr(13) & Chr(10)
Sum3 = Sum3 + "left join (select billtype billtype2,billname from billtype) d on b.billtype=d.billtype2" & Chr(13) & Chr(10)
Sum3 = Sum3 + "left join (select s_id,u_Code,u_Name from product) e on a.prod_id=e.s_id" & Chr(13) & Chr(10)
Sum3 = Sum3 + "where b.s_Syb = 0 and d.billtype2 in (\'15\',\'16\') and (b.billdate >= CONVERT(varchar(100),DateAdd(m,-1,GETDATE()), 23) and b.billdate <= CONVERT(varchar(100), GETDATE(), 23))" & Chr(13) & Chr(10)
Sum3 = Sum3 + "And e.u_Code in (\'240325\',\'240461\',\'244610\',\'243095\',\'246364\',\'248180\',\'248599\',\'013430\',\'012947\',\'115398\')" & Chr(13) & Chr(10)
Sum3 = Sum3 + "GROUP BY Prod_ID" & Chr(13) & Chr(10)
Dim dst As WinForm.DataList = e.Form.Controls("DataList1")
Dim cmd As New SQLCommand
cmd.CommandText = "" & Sum3 & ""
cmd.C
dst.DataTable = cmd.ExecuteReader()
dst.Build()
以上代码正常运行，请问为什么SQL语句要那么麻烦一行一行的拆开再进行组合

--  作者：migold
--  发布时间：2009/8/7 9:38:00
--

我前一阵子，是狐老大，教我用TXT的读取方式，或者调用存储过程

--  作者：migold
--  发布时间：2009/8/7 9:40:00
--
我上次就在想，如果不使用TXT的读取方式，或者调用存储过程，还有什么方式呢？
终于试出以上的方式，可以代替。就是不知道？？？

--  作者：狐狸爸爸
--  发布时间：2009/8/7 9:44:00
--
最开始加一行:
Dim ln AS Srring = Chr(13) & Chr(10)

然后将后面的Chr(13) & Chr(10)用ln代替

--  作者：migold
--  发布时间：2009/8/7 10:12:00
--
Dim ln AS String = Chr(13) & Chr(10)
Dim Sum3 As string = "select Prod_ID," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(Prod_Number,0) else isnull(Prod_Number,0) end) AS \'Prod_Number\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then 0 else isnull(DisMoney,0) end) AS \'DisMoney1\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(DisMoney,0) else 0 end) AS \'DisMoney2\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(DisMoney,0) else isnull(DisMoney,0) end) AS \'DisMoney\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then 0 else isnull(CostMoney,0) end) AS \'CostMoney1\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(CostMoney,0) else 0 end) AS \'CostMoney2\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -isnull(CostMoney,0) else isnull(CostMoney,0) end) AS \'CostMoney\'," & ln
Sum3 = Sum3 + "sum(case when d.billtype2=16 then -(isnull(dismoney,0)-isnull(costmoney,0)) else (isnull(dismoney,0)-isnull(costmoney,0)) end) ProfitMoney," & ln
Sum3 = Sum3 + "\'零售\'AS \'type\',\'最近1个月\' AS \'month\'" & ln
Sum3 = Sum3 + "from ListSale A" & ln
Sum3 = Sum3 + "left join (select autoid,unit_id,billtype,billdate,s_Syb from masterbill) b on a.bill_id=b.autoid" & ln
Sum3 = Sum3 + "left join (select s_id from units) c on b.unit_id=c.s_id" & ln
Sum3 = Sum3 + "left join (select billtype billtype2,billname from billtype) d on b.billtype=d.billtype2" & ln
Sum3 = Sum3 + "left join (select s_id,u_Code,u_Name from product) e on a.prod_id=e.s_id" & ln
Sum3 = Sum3 + "where b.s_Syb = 0 and d.billtype2 in (\'15\',\'16\') and (b.billdate >= CONVERT(varchar(100),DateAdd(m,-1,GETDATE()), 23) and b.billdate <= CONVERT(varchar(100), GETDATE(), 23))" & ln
Sum3 = Sum3 + "And e.u_Code in (\'240325\',\'240461\',\'244610\',\'243095\',\'246364\',\'248180\',\'248599\',\'013430\',\'012947\',\'115398\')" & ln
Sum3 = Sum3 + "GROUP BY Prod_ID" & ln
Output.Show(Sum3)
是这样？

--  作者：yangming
--  发布时间：2009/8/7 11:09:00
--

以下是引用狐狸爸爸在2009-8-7 9:44:00的发言：
最开始加一行:
Dim ln AS Srring = Chr(13) & Chr(10)

然后将后面的Chr(13) & Chr(10)用ln代替

又学了一招,我怎么就想不到呢,呵呵