专家坐堂-去掉不符合条件的行Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  去掉不符合条件的行  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=96775)

--  作者：huhu
--  发布时间：2017/2/27 19:30:00
--  去掉不符合条件的行

<WebMethod()>
Public Function GetState2Refine(ByVal ddh As String, ByVal ddhh As String, ByVal wlbm As String, ByVal rjbb As String, ByVal lhbbh As String) As String
Dim cnStr As String = "Data Source=172.16.11.201;Initial Catalog=songjiang;Integrated Security=False;User ID=sa;Password=bdcom103liujy;"
Dim cn As New SqlClient.SqlConnection(cnStr)
cn.Open()
Dim adapter, adapter1 As SqlClient.SqlDataAdapter
Dim strSql As String = ""

If ddh = "" Or ddhh = "" Or ddh = "null" Or ddhh = "null" Then
Dim state1 As String
state1 = "订单号或者订单行号不能为空"
Return state1

\'adapter = New SqlClient.SqlDataAdapter("sele ct * from [可用数量表] where 物料编码 = \'" & wlbm & "\' and 软件版本 = \'" & rjbb & "\' and 两化表编号 like \'% " & lhbbh & "%\'", cn)
Else
Dim filter As String = "1=1"
If wlbm = "null" Then
filter &= " and 物料编码 is null"
ElseIf wlbm > "" Then
filter &= " and Convert(varchar, 物料编码) = \'" & wlbm & "\'"
End If

If lhbbh = "null" Then
filter &= " and 两化表编号 is null"
ElseIf lhbbh > "" Then
filter &= " and Convert(varchar, 两化表编号) = \'" & lhbbh & "\'"
End If

If rjbb = "null" Then
filter &= " and 软件版本 is null"
ElseIf rjbb > "" Then
filter &= " and Convert(varchar, 软件版本) = \'" & rjbb & "\'"
End If
Dim filter1 As String = "(订单号 <> \'" & ddh & "\') and (订单行号 <> \'" & ddhh & "\') and "
MsgBox(filter1)
strSql = "sele ct * from [可用数量表] where " & filter1 & filter
MsgBox(strSql)
adapter = New SqlClient.SqlDataAdapter(strSql, cn)
Dim dt, dt1 As New DataTable
adapter.Fill(dt)
cn.Close()
Dim state As String = OrderQueryBLL.ConvertToJsonObject(dt)
Return state
End If
End Function
比如下面的表。希望找出订单号不等于ddh同时订单行号也不等于ddhh。
订单号=021610056且订单行号=1共有3行。我需要返回的是剩余的2行。
但红色的filter1.就把所有订单号=021610056和订单行号=1都去掉了，所以只返回第一行，其实第三行也应该返回才对。

此主题相关图片如下：table.png

--  作者：有点蓝
--  发布时间：2017/2/27 20:44:00
--

"sele ct [可用数量表].* from [可用数量表] Inner join (Sel ect 主键 from [可用数量表] where 订单号=\'" & ddh  & "\' and 订单行号=\'" & ddhh  & "\') as b
on [可用数量表].主键 <> b.主键 where " & filter

--  作者：huhu
--  发布时间：2017/2/27 23:06:00
--

返回的结果仍然包括"orderNo":"021610056","orderLineNo":"1"。另外怎么都是重复的。
[{"rootWorks":[{"_Identify":"1334","workNo":"SC20161102001","workLineNo":"12","orderNo":"021609076","orderLineNo":"2","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"10","Storage_time":"2017-02-16 18:51:10"},{"_Identify":"1334","workNo":"SC20161102001","workLineNo":"12","orderNo":"021609076","orderLineNo":"2","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"10","Storage_time":"2017-02-16 18:51:10"},{"_Identify":"1334","workNo":"SC20161102001","workLineNo":"12","orderNo":"021609076","orderLineNo":"2","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"10","Storage_time":"2017-02-16 18:51:10"},{"_Identify":"1335","workNo":"SC20161101001","workLineNo":"19","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"16","Storage_time":"2017-02-16 18:51:14"},{"_Identify":"1335","workNo":"SC20161101001","workLineNo":"19","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"16","Storage_time":"2017-02-16 18:51:14"},{"_Identify":"1354","workNo":"SC20160905001","workLineNo":"25","orderNo":"021610007","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"41","Storage_time":"2017-02-16 18:55:06"},{"_Identify":"1354","workNo":"SC20160905001","workLineNo":"25","orderNo":"021610007","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"41","Storage_time":"2017-02-16 18:55:06"},{"_Identify":"1354","workNo":"SC20160905001","workLineNo":"25","orderNo":"021610007","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"41","Storage_time":"2017-02-16 18:55:06"},{"_Identify":"1380","workNo":"SC20161101001","workLineNo":"32","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"183","Storage_time":"2017-02-16 19:07:30"},{"_Identify":"1380","workNo":"SC20161101001","workLineNo":"32","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"183","Storage_time":"2017-02-16 19:07:30"},{"_Identify":"1381","workNo":"096627040","workLineNo":"32","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"1","Storage_time":"2017-02-16 19:07:33"},{"_Identify":"1381","workNo":"096627040","workLineNo":"32","orderNo":"021610056","orderLineNo":"1","productNo":"XKSWI-SWI1774B","swVersion":"同博达","oemSpecialNo":"","count":"1","Storage_time":"2017-02-16 19:07:33"}],"wareCode":"30"}]

strSql = "se lect [可用数量表].* from [可用数量表] Inner join (Se lect _Identify from [可用数量表] where 订单号=\'" & ddh & "\' and 订单行号=\'" & ddhh & "\') as b on [可用数量表]._Identify <> b._Identify where " & filter

此主题相关图片如下：111.png

--  作者：有点蓝
--  发布时间：2017/2/28 8:40:00
--
导这个表部分数据出来测试一下

--  作者：有点色
--  发布时间：2017/2/28 9:27:00
--

参考这里编写sql语句

http://blog.csdn.net/a11085013/article/details/8549256

--  作者：huhu
--  发布时间：2017/2/28 11:17:00
--

测试，真是不对啊。
<WebMethod()>
Public Function GetState2Refine(ByVal ddh As String, ByVal ddhh As String, ByVal wlbm As String, ByVal rjbb As String, ByVal lhbbh As String) As String
Dim cnStr As String = "Data Source=172.16.11.201;Initial Catalog=songjiang;Integrated Security=False;User ID=sa;Password=bdcom103liujy;"
Dim cn As New SqlClient.SqlConnection(cnStr)
cn.Open()
Dim adapter, adapter1 As SqlClient.SqlDataAdapter
Dim strSql As String = ""

If ddh = "" Or ddhh = "" Or ddh = "null" Or ddhh = "null" Then
Dim state1 As String
state1 = "订单号或者订单行号不能为空"
Return state1
Else
Dim filter As String = "1=1"
If wlbm = "null" Then
filter &= " and 物料编码 is null"
ElseIf wlbm > "" Then
filter &= " and Convert(varchar, 物料编码) = \'" & wlbm & "\'"
End If

If lhbbh = "null" Then
filter &= " and 两化表编号 is null"
ElseIf lhbbh > "" Then
filter &= " and Convert(varchar, 两化表编号) = \'" & lhbbh & "\'"
End If

If rjbb = "null" Then
filter &= " and 软件版本 is null"
ElseIf rjbb > "" Then
filter &= " and Convert(varchar, 软件版本) = \'" & rjbb & "\'"
End If
Dim filter1 As String = "(订单号 <> \'" & ddh & "\') or (订单行号 <> \'" & ddhh & "\') and "

strSql = "s elect [可用数量表] * from [可用数量表] Inner join (S elect _Identify from [可用数量表] where 订单号=\'" & ddh & "\' and 订单行号=\'" & ddhh & "\') as b on [可用数量表]._Identify <> b._Identify where " & filter


MsgBox(strSql)

adapter = New SqlClient.SqlDataAdapter(strSql, cn)

Dim dt As New DataTable
adapter.Fill(dt)

cn.Close()

Dim state As String = OrderQueryBLL.ConvertToJsonObject(dt)
Return state

End If
End Function哪里出问题了?

--  作者：有点蓝
--  发布时间：2017/2/28 11:36:00
--

导出数据测试

--  作者：huhu
--  发布时间：2017/2/28 11:36:00
--

此主题相关图片如下：table.png

此主题相关图片如下：111.png

此主题相关图片如下：2.png

请帮忙看看。

--  作者：huhu
--  发布时间：2017/2/28 11:37:00
--
什么是导出数据测试?
我现在就是用真实的数据测试的。

--  作者：huhu
--  发布时间：2017/2/28 11:40:00
--

此主题相关图片如下：table1.png

红色的三行需要去掉。只找出剩余的2行。