| 以文本方式查看主题 - Foxtable(狐表) (http://foxtable.com/bbs/index.asp) -- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2) ---- [求助] SQL 条件错误 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=152803) |
| -- 作者：susanhe -- 发布时间：2020/7/22 17:55:00 -- [求助] SQL 条件错误 老师，以下黄色的错了，帮忙指导一下，谢谢。 cmd.CommandText ="Se lect {t_Item}.FNumber,{t_Item}.FName,FShortName From ((({ICStockBillEntry} Inner JOIN {t_Item} ON {t_Item}.[FItemID] = {ICStockBillEntry}.[FItemID]) Inner JOIN {ICStockBill} ON {ICStockBill}.[FInterID] = {ICStockBillEntry}.[FInterID]) Left JOIN {POOrderEntry} ON {POOrderEntry}.[FInterID] = {ICStockBillEntry}.[FOrderInterID] And {POOrderEntry}.[FEntryID] = {ICStockBillEntry}.[FOrderEntryID]) Inner JOIN {t_Supplier} ON {t_Supplier}.[FItemID] = {ICStockBill}.[FSupplyID] Where {ICStockBill}.[FBillNo] like \'%WIN%\'and {ICStockBillEntry}.[FQty] > 0 and {ICStockBill}.[FDate] < = \'" & e.Form.Controls("Datetimepicker1").Value & "\' And {ICStockBill}.[FDate] > = \'" & e.Form.Controls("Datetimepicker2").Value & "\' Group By {t_Item}.FNumber,{t_Item}.FName,FShortName" |
| -- 作者：linyunu1 -- 发布时间：2020/7/22 18:31:00 -- 提示什么错误 |
| -- 作者：susanhe -- 发布时间：2020/7/22 18:53:00 -- 结果没有出来，错误信息也没有。 |
| -- 作者：有点蓝 -- 发布时间：2020/7/22 20:26:00 -- 不加上黄色代码就可以？说明没有符合条件的数据 |
| -- 作者：susanhe -- 发布时间：2020/7/23 7:25:00 -- 对的，不加黄色的话全部出来了。但是这个时间期是有记录的，是不是日期格式不对 ？ |
| -- 作者：有点蓝 -- 发布时间：2020/7/23 8:40:00 -- 日期列值有时分秒？ |
| -- 作者：susanhe -- 发布时间：2020/7/24 12:30:00 -- 老师，找到原因了。这个 大于和小于弄反了。改过来就好了。 还有个问题,我要统计这其中每个产品的总量，可是不对。麻烦一下。 Dim val As Integer Dim cmdz As New SQLCommand cmdz.Co nnecti cmdz.CommandText = "Sel ect {t_Item}.FNumber,{t_Item}.FName,FShortName,Sum({ICStockBillEntry}.[FQty]) From ((({ICStockBillEntry} Inner JOIN {t_Item} ON {t_Item}.[FItemID] = {ICStockBillEntry}.[FItemID]) Inner JOIN {ICStockBill} ON {ICStockBill}.[FInterID] = {ICStockBillEntry}.[FInterID]) Left JOIN {POOrderEntry} ON {POOrderEntry}.[FInterID] = {ICStockBillEntry}.[FOrderInterID] And {POOrderEntry}.[FEntryID] = {ICStockBillEntry}.[FOrderEntryID]) Inner JOIN {t_Supplier} ON {t_Supplier}.[FItemID] = {ICStockBill}.[FSupplyID] Where {ICStockBill}.[FBillNo] like \'%WIN%\'and {ICStockBillEntry}.[FQty] > 0 and {ICStockBill}.[FDate] > = \'" & e.Form.Controls("Datetimepicker1").Value & "\' And {ICStockBill}.[FDate] < = \'" & e.Form.Controls("Datetimepicker2").Value & "\'" val = cmdz.ExecuteScalar() For Each rr As Row In Tables("PPV").Rows rr("数量总计") =sum(val("FQty")) Next
|
| -- 作者：有点蓝 -- 发布时间：2020/7/24 13:34:00 -- Tables("PPV")这个表的数据和这段sql有什么关系？截图看看 |
| -- 作者：susanhe -- 发布时间：2020/7/24 14:35:00 -- 老师，前面还有这段，谢谢了。 Dim cmd As New SQLCommand Dim dt As DataTable DataTables("PPV").datarows.clear cmd.C \'外购入库单 Dim Cols111() As String = {"FNumber","FName","FShortName"} Dim Cols222() As String = {"GSSPartNo","PartDesc","Supplier"} cmd.CommandText ="Sel ect {t_Item}.FNumber,{t_Item}.FName,FShortName From ((({ICStockBillEntry} Inner JOIN {t_Item} ON {t_Item}.[FItemID] = {ICStockBillEntry}.[FItemID]) Inner JOIN {ICStockBill} ON {ICStockBill}.[FInterID] = {ICStockBillEntry}.[FInterID]) Left JOIN {POOrderEntry} ON {POOrderEntry}.[FInterID] = {ICStockBillEntry}.[FOrderInterID] And {POOrderEntry}.[FEntryID] = {ICStockBillEntry}.[FOrderEntryID]) Inner JOIN {t_Supplier} ON {t_Supplier}.[FItemID] = {ICStockBill}.[FSupplyID] Where {ICStockBill}.[FBillNo] like \'%WIN%\'and {ICStockBillEntry}.[FQty] > 0 and {ICStockBill}.[FDate] > = \'" & e.Form.Controls("Datetimepicker1").Value & "\' And {ICStockBill}.[FDate] < = \'" & e.Form.Controls("Datetimepicker2").Value & "\' Group By {t_Item}.FNumber,{t_Item}.FName,FShortName" dt = cmd.ExecuteReader() For Each dr1 As DataRow In dt.DataRows Dim dr2 As DataRow = DataTables("PPV").AddNew() For i As Integer = 0 To Cols111.Length -1 dr2(Cols222(i)) = dr1(Cols111(i)) Next Next Tables("PPV").ResumeRedraw()
|
| -- 作者：有点蓝 -- 发布时间：2020/7/24 14:44:00 -- 为什么不在一条SQL里直接分组统计 ？而要分开2次处理？ Dim Cols111() As String = {"FNumber","FName","FShortName"}
Dim Cols222() As String = {"GSSPartNo","PartDesc","Supplier"} cmd.CommandText ="Sel ect {t_Item}.FNumber,{t_Item}.FName,FShortName,Sum({ICStockBillEntry}.[FQty]) as FQty From ((({ICStockBillEntry} Inner JOIN {t_Item} ON { |