分组内排序（分组内有重复的编号一致） - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

要求在饰品类别分组，按件数进行排序且件数相等，序号相同

以下代码执行后无法实现，求老师解答

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 Select

老师，代码执行后，序号还是不一致
图片点击可在新窗口打开查看

此主题相关图片如下：1588063430(1).jpg

数据不是录入的，是通过代码加载进来的

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))

而且我试了手动录入数据时，只要输入饰品类别列内容还没输入件数数，序号就生成了，是这里的原因？

[此贴子已经被作者于2020/4/28 17:17:30编辑过]

可以了，谢谢老师

主题：分组内排序（分组内有重复的编号一致）