MYSQL 排序和分隔

Question

我在排序行号时遇到问题。我的代码输出如下

(801;802;803;804;805;806;807;808-814(1);808-814(2);815;;;;;;;;;;;;;;;;;840) 

其分隔符将循环直到840。

Dim sqlline As DataTable = MyDB.ExecCommand("SELECT `Line Number` from `" + cboJob.Text + "` WHERE `Orig Document Begin ID`='" + mData.Rows(z).Item(0).ToString.Trim + "' ORDER BY `Line Number`", "wellsfargo").Tables(0)
                    For q As Integer = 0 To sqlline.Rows.Count - 1
                        If sqlline.Rows.Count <> 0 Then

                            If q = 0 Then
                                lNum = sqlline.Rows(q).Item(0).ToString
                            Else
                                lNum += IIf(dZ.Rows(q).Item(13).ToString <> "", ";" + sqlline.Rows(q).Item(0).ToString, ";")
                            End If
                            'lNum += IIf(sqlline.Rows(q).Item(0).ToString <> "", ";" + sqlline.Rows(q).Item(0).ToString, "")

                        End If
                    Next

但我希望我的输出是这样的。

(801;802;803;804;805;806;807;815;;;;;;;;;;;;;;;;;;;;;840;808-814(1);808-814(2))

因此所有行号都将位于末尾。

现在..编辑我有这个代码供我在单行号末尾添加空格分隔符:

  Dim sCont As String = ""
            If dZ.Rows.Count < 40 Then
                Dim iCont As Integer = 40 - dZ.Rows.Count
                For c As Integer = 0 To iCont - 1
                    If c = 0 Then
                        sCont = ";"
                    Else
                        sCont += ";"
                    End If
                Next
                'Loop then Concatinate strings for each field value.
            ElseIf dZ.Rows.Count = 40 Then
                'Loop as is...
            End If

            Dim sVal As String()
            If dZ.Rows.Count < 40 Then
                sVal = (OrgDocbeg + "■" + _
                                OrgDocend + "■" + _
                                BEGDOC + "■" + _
                                ENDDOC + "■" + _
                                LoanNum + "■" + _
                                PCount + "■" + _
                                Path + "■" + _
                                FNum + "■" + _
                                sDate + "■" + _
                                StrConv(LNF, VbStrConv.ProperCase) + "■" + _
                                lNum + sCont + "■" + _
                                sDesc + sCont + "■" + _
                                StrConv(Amount, VbStrConv.ProperCase).Replace("Poc", "POC").Replace(".00.00", "") + sCont + "■" + _
                                sPay.Replace("^", "").Replace(" Of ", " of ").Replace(" And ", " and ").Replace(" And/or", " and/or").Replace(" By ", " by ").Replace(" 2Nd ", " 2nd ").Replace(" 3Rd ", " 3rd ") + sCont + "■" + _
                                sBorrow.Replace("$.00", "$0.00") + sCont + "■" + _
                                sSell + sCont + "■" + _
                                ProsBor + sCont + "■" + _
                                ProSell + sCont).Split("■")

            Else
                sVal = (OrgDocbeg + "■" + _
                                OrgDocend + "■" + _
                                BEGDOC + "■" + _
                                ENDDOC + "■" + _
                                LoanNum + "■" + _
                                PCount + "■" + _
                                Path + "■" + _
                                FNum + "■" + _
                                sDate + "■" + _
                                StrConv(LNF, VbStrConv.ProperCase) + "■" + _
                                lNum + "■" + _
                                sDesc + "■" + _
                                sPay.Replace("^", "").Replace(" Of ", " of ").Replace(" And ", " and ").Replace(" And/or", " and/or").Replace(" By ", " by ").Replace(" 2Nd ", " 2nd ").Replace(" 3Rd ", " 3rd ") + "■" + _
                                StrConv(Amount, VbStrConv.ProperCase).Replace("Poc", "POC").Replace("$.", "$0.") + "■" + _
                                sBorrow.Replace("$.", "$0.") + "■" + _
                                sSell.Replace("$.", "$0.") + "■" + _
                                ProsBor.Replace("$.", "$0.") + "■" + _
                                ProSell.Replace("$.", "$0.")).Split("■")
            End If

但是我的输出是错误的:

 801;802;803;804;;;;;;;;808 - 817(1);808 - 817(2);808 - 817(3);808 - 817(4);808 - 817(5);808 - 817(6);;;;;;;;;;;;;;;;;;;;;;;

如果行号中有范围(必须有分隔符直到 840)，输出应该是这样的

801;802;803;804;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;808 - 817(1);808 - 817(2);808 - 817(3);808 - 817(4);808 - 817(5);808 - 817(6)

但是如果我没有范围，正确的输出将是

801;802;803;804

Answer 1

如果您的行代码具有固定长度，那么您可以根据长度拆分结果集，独立排序，然后使用 UNION ALL 将它们合并:

Dim strQuery = "(SELECT `Line Number` FROM `" + cboJob.Text + _ 
"` WHERE LOCATE('-', line) = 0 AND `Orig Document Begin ID`='" + mData.Rows(z).Item(0).ToString.Trim + _
" ORDER BY `Line Number`) " + _ 
"UNION ALL " + _
"(SELECT `Line Number` FROM `" + cboJob.Text + _ 
"` WHERE LOCATE('-', line) > 0 AND `Orig Document Begin ID`='" + mData.Rows(z).Item(0).ToString.Trim + _
" ORDER BY `Line Number`)"

Dim sqlline As DataTable = MyDB.ExecCommand(strQuery, "wellsfargo").Tables(0)