- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 分组内排序（分组内有重复的编号一致） (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=149307)
-- 发布时间：2020/4/28 15:42:00
-- 分组内排序（分组内有重复的编号一致）
要求在饰品类别分组，按件数进行排序且件数相等，序号相同
以下代码执行后无法实现，求老师解答
Dim dr As DataRow = e.DataRow
Select Case e.DataCol.Name
Case "饰品类别","件数"
If e.DataCol.Name = "饰品类别" Then
If e.DataRow.IsNull("饰品类别") Then
e.DataRow("序号") = Nothing
Else
Dim max As String
Dim idx As Integer
max = e.DataTable.Compute("Max(序号)","饰品类别 = \'" & e.DataRow("饰品类别") & "\' And [_Identify] <> " & e.DataRow("_Identify")) \'取得该天的最大序号
If max > "" Then \'如果存在最大序号
If e.DataTable.Compute("Count([_Identify])","饰品类别 = \'" & dr("饰品类别") & "\' And 件数 = \'" & dr("件数") & "\'") > 1 Then
e.DataRow("序号") = Format(max,"00000")
Else
idx = CInt(max.Substring(0,5)) + 1 \'获得最大序号的后三位顺序号,并加1
End If
Else
idx = idx \'否则顺序号等于1
End If
e.DataRow("序号") = Format(idx,"00000")
End If
End If
End Select
-- 发布时间：2020/4/28 16:03:00
--
Dim dr As DataRow = e.DataRow
Select Case e.DataCol.Name
Case "饰品类别","件数"
If e.DataCol.Name = "饰品类别" Then
If e.DataRow.IsNull("饰品类别") Then
e.DataRow("序号") = Nothing
Else
Dim dr2 As DataRow = e.DataTable.find("饰品类别 = \'" & dr("饰品类别") & "\' And 件数 = \'" & dr("件数") & "\' And [_Identify] <> " & dr("_Identify"))
If dr2 IsNot Nothing AndAlso dr2.IsNull("序号") = False
e.DataRow("序号") = dr2("序号")
Else
Dim max As String
Dim idx As Integer
max = e.DataTable.Compute("Max(序号)","饰品类别 = \'" & e.DataRow("饰品类别") & "\' And [_Identify] <> " & e.DataRow("_Identify")) \'取得该天的最大序号
If max > "" Then \'如果存在最大序号
idx = CInt(max) + 1 \'获得最大序号的后三位顺序号,并加1
Else
idx = 1 \'否则顺序号等于1
End If
e.DataRow("序号") = Format(idx,"00000")
End If
End If
End If
End Select
-- 发布时间：2020/4/28 16:45:00
--
老师，代码执行后，序号还是不一致
此主题相关图片如下：1588063430(1).jpg
-- 发布时间：2020/4/28 16:59:00
--
做个例子发上来测试。旧数据不会自己更改的，新录入的数据有没有问题？
-- 发布时间：2020/4/28 17:15:00
--
数据不是录入的，是通过代码加载进来的
Dim xsml As New SQLCommand \'销量查询表
Dim ml As DataTable
Dim sCols() As String = {"饰品编号","饰品类别","数量"}
Dim dCols() As String = {"物料编号","饰品类别","件数"}
xsml.CommandText = "查询 sum(quantity) As [数量],{R05_RetailShipment_Detail}.itemID As [饰品编号],kindName As [饰品类别] From ({R05_RetailShipment_Detail} Inner JOIN {R05_RetailShipment} ON {R05_RetailShipment}.[smartID] = {R05_RetailShipment_Detail}.[smartID]) Inner JOIN {R02_SilverStyle} ON {R02_SilverStyle}.[itemID] = {R05_RetailShipment_Detail}.[itemID] Where {R05_RetailShipment}.smartDate >=\'2019-03-01 00:00:00\' and {R05_RetailShipment}.smartDate <=\'2020-03-31 23:59:00\' And customerCode In (" & ids & ") and warehouse1Code=\'02\' Group By {R05_RetailShipment_Detail}.itemID,kindName order by 饰品类别,数量 desc"
ml = xsml.ExecuteReader()
For Each dr As DataRow In ml.DataRows
Dim nr As DataRow = DataTables("销量查询表").AddNew()
For i As Integer =0 To sCols.Length -1
nr(dCols(i)) = dr(sCols(i))
Next
Next
而且我试了手动录入数据时，只要输入饰品类别列内容还没输入件数数，序号就生成了，是这里的原因？
[此贴子已经被作者于2020/4/28 17:17:30编辑过]
-- 发布时间：2020/4/28 17:23:00
--
Dim dr As DataRow = e.DataRow
Select Case e.DataCol.Name
Case "饰品类别","件数"
If e.DataRow.IsNull("饰品类别") orelse e.DataRow.IsNull("件数") Then
e.DataRow("序号") = Nothing
Else
Dim dr2 As DataRow = e.DataTable.find("饰品类别 = \'" & dr("饰品类别") & "\' And 件数 = \'" & dr("件数") & "\' And [_Identify] <> " & dr("_Identify"))
If dr2 IsNot Nothing AndAlso dr2.IsNull("序号") = False
e.DataRow("序号") = dr2("序号")
Else
Dim max As String
Dim idx As Integer
max = e.DataTable.Compute("Max(序号)","饰品类别 = \'" & e.DataRow("饰品类别") & "\' And [_Identify] <> " & e.DataRow("_Identify")) \'取得该天的最大序号
If max > "" Then \'如果存在最大序号
idx = CInt(max) + 1 \'获得最大序号的后三位顺序号,并加1
Else
idx = 1 \'否则顺序号等于1
End If
e.DataRow("序号") = Format(idx,"00000")
End If
End If
End Select
-- 发布时间：2020/4/28 17:29:00
--
可以了，谢谢老师