vb.net 2017使用复杂的SQL查询语句日期跨度过长的话查询无结果
有没有哪位大神帮帮忙,帮我分析一下,我写了一段SQL查询语句,在vb.net中执行,如果时间跨度在2个月之内,查询有结果,但如果时间跨度过大,查询时间超过半分钟,则无任何查询结果。代码如下:
Dim sql As String
Dim dataAdapter As New SqlClient.SqlDataAdapter
Dim dst As New DataSet
Dim dt As New DataTable
Dim conn As New SqlClient.SqlConnection
Try
conn = New SqlClient.SqlConnection("Data Source=192.168.0.5;Initial Catalog=hycfb;User ID=sa;Password=gythymzjs;Integrated Security=False")
conn.Open()
sql = "select b.REGISTERSUBORGAN 门店编号,g.zjm 门店助记,g.mc 门店名称," & vbCr &
"b.hygs as 会员总个数 ,b.xzhygs 新增会员个数," & vbCr &
"a.hdhygs 活动会员个数," & vbCr &
"(case when isnull(b.hygs,0)=0 then 0 else round(100*isnull(a.hdhygs,0)/(cast(b.hygs as decimal(18,2))),2) end) 活动会员占比," & vbCr &
"b.T90wd 无销90天会员个数," & vbCr &
"b.lshys 会员流失个数," & vbCr &
"(case when isnull(b.t270cs,0)=0 then 0 else round(100*isnull(b.lshys,0)/(cast(b.t270cs as decimal(18,2))),2) end) 会员流失率," & vbCr &
"a.jshj 销售额,a.hy_jshj 会员销额," & vbCr &
"(case when isnull(a.jshj,0)=0 then 0 else round(100*(isnull(a.hy_jshj,0))/isnull(a.jshj,0),2) end ) 会员占总销比," & vbCr &
"a.hsmle 含税毛利,a.hy_hsmle 会员毛利," & vbCr &
"(case when isnull(a.hsmle,0)=0 then 0 else round(100*(isnull(a.hy_hsmle,0))/isnull(a.hsmle,0),2) end ) 会员占总毛利比," & vbCr &
"(case when isnull(a.jshj,0)=0 then 0 else round(100*(isnull(a.hsmle,0))/isnull(a.jshj,0),2) end ) 毛利率," & vbCr &
"(case when isnull(a.hy_jshj,0)=0 then 0 else round(100*(isnull(a.hy_hsmle,0))/isnull(a.hy_jshj,0),2) end ) 会员毛利率," & vbCr &
"a.khs 顾客数,a.hy_khs 会员客次," & vbCr &
"(case when isnull(a.khs,0)=0 then 0 else round(100*(isnull(a.hy_khs,0))/(cast(isnull(a.khs,0) as decimal(18,2))),2) end ) 会员占总顾客数比," & vbCr &
"(case when isnull(a.khs,0)=0 then 0 else round(isnull(a.jshj,0)/a.khs,2) end) 客单价 ," & vbCr &
"(case when isnull(a.dhs,0)=0 then 0 else round(isnull(a.jshj,0)/a.dhs,2) end) 客品单价 ," & vbCr &
"(case when isnull(a.khs,0)=0 then 0 else round(isnull(a.sl,0)/a.khs,2) end) 客品数量 ," & vbCr &
"(case when isnull(a.sl,0)=0 then 0 else round(isnull(a.jshj,0)/a.sl,2) end) 商品单价 ," & vbCr &
"(case when isnull(a.hy_khs,0)=0 then 0 else round(isnull(a.hy_jshj,0)/a.hy_khs,2) end) 会员客单价 ," & vbCr &
"(case when isnull(a.fhy_khs,0)=0 then 0 else round(isnull(a.fhy_jshj,0)/a.fhy_khs,2) end) 非会员客单价 ," & vbCr &
"(case when isnull(a.hy_dhs,0)=0 then 0 else round(isnull(a.hy_jshj,0)/a.hy_dhs,2) end) 会员客品单价 ," & vbCr &
"(case when isnull(a.fhy_dhs,0)=0 then 0 else round(isnull(a.fhy_jshj,0)/a.fhy_dhs,2) end) 非会员客品单价 ," & vbCr &
"(case when isnull(a.hy_khs,0)=0 then 0 else round(isnull(a.hy_sl,0)/a.hy_khs,2) end) 会员客品数量 ," & vbCr &
"(case when isnull(a.fhy_khs,0)=0 then 0 else round(isnull(a.fhy_sl,0)/a.fhy_khs,2) end) 非会员客品数量 ," & vbCr &
"(case when isnull(a.hy_sl,0)=0 then 0 else round(isnull(a.hy_jshj,0)/a.hy_sl,2) end) 会员商品单价 ," & vbCr &
"(case when isnull(a.fhy_sl,0)=0 then 0 else round(isnull(a.fhy_jshj,0)/a.fhy_sl,2) end) 非会员商品单价 ," & vbCr &
"(case when isnull(a.khs,0)=0 then 0 else round(isnull(a.gk_sku,0)/(cast(a.khs as decimal(18,2))),2) end) 客次SKU数," & vbCr &
" a.一线1类销售,a.一线2类销售,a.普通品种销售,a.主次推品销售额," & vbCr &
"(case when isnull(a.jshj,0)=0 then 0 else round(100*(isnull(a.主次推品销售额,0))/isnull(a.jshj,0),2) end ) 推品占总销比," & vbCr &
"a.会员主次推品销售额 as 会员推品销售," & vbCr &
"(case when isnull(a.hy_jshj,0)=0 then 0 else round(100*(isnull(a.会员主次推品销售额,0))/isnull(a.hy_jshj,0),2) end ) 会员推品占会员销比 " & vbCr &
" from " & vbCr &
"(" & vbCr &
"select a.REGISTERSUBORGAN," & vbCr &
"sum(case when a.FDELETED=1 or a.FFORBID=1 or a.createdate='1900-01-01' then 0 else 1 end ) hygs ," & vbCr &
"count(b.t270cs) t270cs," & vbCr &
"sum(case when a.FDELETED=1 or a.FFORBID=1 or a.createdate='1900-01-01' then 0 when a.createdate between '" & DateTimePicker1.Value & "' and '" & DateTimePicker2.Value & "' then 1 else 0 end ) xzhygs ," & vbCr &
"sum(case when a.FDELETED=1 or a.FFORBID=1 or a.LOCALUPDATED is null or a.LOCALUPDATED='1900-01-01'" & vbCr &
"then 0 when cast(getdate()-a.LOCALUPDATED as int )>90 then 1 else 0 end) T90wd," & vbCr &
"sum(case when a.FDELETED=1 or a.FFORBID=1 or a.LOCALUPDATED is null or a.LOCALUPDATED='1900-01-01' " & vbCr &
"then 0 when cast(getdate()-a.LOCALUPDATED as int )>90 and isnull(b.t270cs,0)>=1 then 1 else 0 end) lshys" & vbCr &
"from gl_hy a " & vbCr &
"left join (select branchguid,vipid,count(billno) t270cs from vipbill where Ftype='0' " & vbCr &
"and billdate between getdate()-365 and getdate()-90 group by branchguid, vipid)" & vbCr &
"b on a.id= b.vipid and a.REGISTERSUBORGAN=b.branchguid " & vbCr &
"group by a.REGISTERSUBORGAN " & vbCr &
") b " & vbCr &
"left join " & vbCr &
"(select a.subbh ," & vbCr &
"sum(a.jshj) jshj ,sum(round(a.ml*(a.zzsl+100)/100,2)) hsmle," & vbCr &
"sum(case when a.YHKH is not null then a.jshj else 0 end) hy_jshj ," & vbCr &
"sum(case when a.YHKH is null then a.jshj else 0 end) fhy_jshj ," & vbCr &
"sum(case when a.YHKH is not null then round(a.ml*(a.zzsl+100)/100,2) else 0 end) hy_hsmle ," & vbCr &
"count(distinct a.lsh+a.hh) gk_sku," & vbCr &
"count(distinct a.lsh) as khs," & vbCr &
"count(distinct (case when a.YHKH is not null then a.lsh else 'a' end))-1 as hy_khs," & vbCr &
"count(distinct (case when a.YHKH is null then a.lsh else 'a' end))-1 as fhy_khs," & vbCr &
"count(distinct (case when a.YHKH is not null then a.dh else 'a' end))-1 as hy_dhs," & vbCr &
"count(distinct (case when a.YHKH is null then a.dh else 'a' end))-1 as fhy_dhs," & vbCr &
"count(distinct (case when a.YHKH is not null then a.yhkh else 'a' end))-1 as hdhygs," & vbCr &
"count(a.dh) as dhs,sum(a.sl) sl," & vbCr &
"sum(case when a.YHKH is not null then a.sl else 0 end) hy_sl ," & vbCr &
"sum(case when a.YHKH is null then a.sl else 0 end) fhy_sl ," & vbCr &
"sum(case when pp.code='1' then a.jshj else 0 end ) 一线1类销售," & vbCr &
"sum(case when pp.code='2' then a.jshj else 0 end ) 一线2类销售," & vbCr &
"sum(case when pp.code<>'1' and pp.code<>'2' or pp.code is null then a.jshj else 0 end ) 普通品种销售," & vbCr &
"sum(case when m.code='01' or m.code='02' then a.jshj else 0 end ) 主次推品销售额," & vbCr &
"sum(case when a.YHKH is not null and (m.code='01' or m.code='02' ) then a.jshj else 0 end ) 会员主次推品销售额" & vbCr &
" from subfhd a left join yw_kck d on a.hh=d.hh" & vbCr &
" Left Join (Select Code,Name From Dataitem Where Dataitemtypeguid='311') M On D.Ztflsx=M.Code " & vbCr &
"Left Join (Select guid,Code,Name from Dataitem where dataitemtypeguid='20000535' ) pp On D.BrandAttribute=pp.guid " & vbCr &
"where a.kdrq between '" & DateTimePicker1.Value & "' and '" & DateTimePicker2.Value & "' " & vbCr &
"/*and ('[门店编号]'='' or a.subbh='[门店编号]') */" & vbCr &
"group by a.subbh" & vbCr &
") a on b.REGISTERSUBORGAN=a.subbh" & vbCr &
"left join gl_custom g on b.REGISTERSUBORGAN=g.tjbh" & vbCr &
"order by a.subbh"
Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, conn)
dataAdapter.SelectCommand = cmd
dataAdapter.Fill(dst, "info")
dt = dst.Tables("info")
DataGridView1.AutoGenerateColumns = True
DataGridView1.DataSource = dt
conn.Close()
Catch ex As Exception
End Try
'Dim count As Integer = Me.DataGridView1.Rows.Count
''Dim sum As Double = 0
'For i = 0 To count - 1
' sum += Val(Me.DataGridView1.Item("销售额", i).Value)
'Next
'ToolStripStatusLabel3.Text = ToolStripStatusLabel3.Text + sum.ToString
End Sub
本人新手,求指点!!!!不胜感激!!!