以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  条件筛选问题  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=72573)
--  作者：don
--  发布时间：2015/8/1 20:01:00
--  
Dim SQL,s1 As String
SQL = "Select  *  Fr"  &  "om{表A} Where 数量 <=9"

Dim cmd As New SQLCommand
Dim dt As DataTable
Dim i As Integer =0

cmd.CommandText = SQL
dt = cmd.ExecuteReader()
Dim drs2 As List(of DataRow) = dt.Select("","物品,次序")

SQL =Nothing
s1 = " Union all S" & "elect 物品,次序,数量 Fr"  &  "om{表A} Where [_Identify] =  @ID"

For Each dr1 As DataRow In drs2
    If  i >0  AndAlso dr1("物品") = drs2(i-1)("物品") AndAlso dr1("次序") = drs2(i-1)("次序")+1 Then
        SQL+=s1.replace("@ID",drs2(i-1)("_Identify") )
        SQL+=s1.replace("@ID",dr1("_Identify") )
    End If
    i+=1
Next


If SQL IsNot Nothing Then
    SQL= SQL.SubString(11)
    Output.Show(SQL)
    
End If
[此贴子已经被作者于2015/8/1 20:01:35编辑过]
--  作者：大红袍
--  发布时间：2015/11/10 14:29:00
--  

Dim SQL,s1 As String
SQL = "Select  *  Fr"  &  "om{表A} Where 数量 <=9"


Dim cmd As New SQLCommand
Dim dt As DataTable
Dim i As Integer =0


cmd.CommandText = SQL
dt = cmd.ExecuteReader()
Dim drs2 As List(of DataRow) = dt.Select("","物品,次序")


SQL =Nothing
s1 = " Union all S" & "elect 物品,次序,数量 Fr"  &  "om{表A} Where [_Identify] =  @ID"

Dim count As Integer = 1
For Each dr1 As DataRow In drs2
    If  i >0  AndAlso dr1("物品") = drs2(i-1)("物品") AndAlso dr1("次序") = drs2(i-1)("次序")+1 Then
        count += 1
        If count = 3 Then
            SQL+=s1.replace("@ID",drs2(i-2)("_Identify") )
            SQL+=s1.replace("@ID",drs2(i-1)("_Identify") )
            SQL+=s1.replace("@ID",dr1("_Identify") )
            count = 1
        End If
    End If
    i+=1
Next

 


If SQL IsNot Nothing Then
    SQL= SQL.SubString(11)
    Output.Show(SQL)
   
End If