关于vb中提取数据库中日期的年月··进行数据分组操作··
我想通过 Mdatas2 = SQL_READ("select format(T_结账单.F_日期,'yyyy-MM') AS F_日期2,SUM(F_服务费) AS F_总服务费,SUM(F_折扣费) AS F_总折扣费,SUM(F_收款金额) AS F_总收款金额,SUM(F_人数) AS F_总人数 from T_结账单 WHERE F_日期2='" & P_DATA & "'GROUP BY F_日期2 ORDER BY F_日期2")
取出年月相等的数据进行操作，但是执行就发生提示：
试图执行的查询中不包含作为合计函数一部分的特定表达式'format(T_结账单.F_日期,'yyyy-MM')'.
Setgrid_data_当月(ByRef OBJ As Object, ByVal P_DATA As String)
Dim i, i1, i2 As Integer
Dim S1, S2, SUM As String
Dim s As Double
Dim Mdb As New MYdatabase
Dim Mdatas As New DataSet
Dim Mdatas1 As New DataSet
Dim Mdatas2 As New DataSet
Mdatas1 = SQL_READ("select f_编码,F_名称 from T_菜类名称 ORDER BY f_编码")
Mdatas2 = SQL_READ("select format(T_结账单.F_日期,'yyyy-MM') AS F_日期2,SUM(F_服务费) AS F_总服务费,SUM(F_折扣费) AS F_总折扣费,SUM(F_收款金额) AS F_总收款金额,SUM(F_人数) AS F_总人数 from T_结账单 WHERE F_日期2='" & P_DATA & "'GROUP BY F_日期2 ORDER BY F_日期2")
Mdatas = SQL_READ("select F_菜类编码,F_名称,SUM(F_总价) AS F_各类金额,format(T_结账消费明细.F_日期,'yyyy-MM')AS F_日期1 from T_结账消费明细,T_菜类名称 where T_结账消费明细.F_菜类编码=T_菜类名称.F_编码 GROUP BY F_日期1,F_菜类编码,F_名称 ORDER BY F_日期1,F_菜类编码")
Setdgrid_View(OBJ, 0) '初始化结构
Setdgrid_Head(OBJ, "日期", "F_日期1", 60) ' ；设置标题
' Setdgrid_Head(OBJ, "房号", "F_房号", 60)
Setdgrid_Head(OBJ, "人数", "F_人数", 60)
For i1 = 0 To Mdatas1.Tables(0).Rows.Count - 1 '菜类名称Mdatas1
S1 = Mdatas1.Tables(0).Rows(i1).Item("F_名称").ToString()
Setdgrid_Head(OBJ, S1, S1, 60)
Next
Setdgrid_Head(OBJ, "服务费", "F_总服务费", 60)
Setdgrid_Head(OBJ, "折扣费", "F_总折扣费", 60)
Setdgrid_Head(OBJ, "合计", "F_总收款金额", 60)
' OBJ.Items.Clear()
'******************************************
'***************************************
For i2 = 0 To Mdatas2.Tables(0).Rows.Count - 1
S2 = Mdatas2.Tables(0).Rows(i2).Item("F_日期2").ToString() '结账消费明细
Dim row As New DataGridViewRow
row.CreateCells(OBJ)
row.Cells(0).Value = Mdatas2.Tables(0).Rows(i2).Item("F_日期2").ToString()
' row.Cells(1).Value = Mdatas2.Tables(0).Rows(i2).Item("F_房号").ToString()
row.Cells(1).Value = Mdatas2.Tables(0).Rows(i2).Item("F_总人数").ToString()
For i1 = 0 To Mdatas1.Tables(0).Rows.Count - 1 '菜类名称
S2 = Mdatas2.Tables(0).Rows(i2).Item("F_日期2").ToString()
S1 = Mdatas1.Tables(0).Rows(i1).Item("F_编码").ToString()
SUM = ""
For i = 0 To Mdatas.Tables(0).Rows.Count - 1
If Mdatas.Tables(0).Rows(i).Item("F_菜类编码").ToString() = S1 And Mdatas.Tables(0).Rows(i).Item("F_日期1").ToString() = S2 Then
s = FormatNumber(Mdatas.Tables(0).Rows(i).Item("F_各类金额"), 2)
SUM = s.ToString 'Mdatas.Tables(0).Rows(i).Item("F_各类金额").ToString()
row.Cells(2 + i1).Value = SUM
End If
Next
Next
i1 = Mdatas1.Tables(0).Rows.Count
row.Cells(1 + i1 + 1).Value = Mdatas2.Tables(0).Rows(i2).Item("F_总服务费").ToString()
row.Cells(1 + i1 + 2).Value = Mdatas2.Tables(0).Rows(i2).Item("F_总折扣费").ToString()
row.Cells(1 + i1 + 3).Value = Mdatas2.Tables(0).Rows(i2).Item("F_总收款金额").ToString()
OBJ.Rows.Add(row)
Next
'******************************************
'***************************************
End Sub
Function GET_DATE() As String
Dim P_DATE As String
'P_DATE = Me.DateTimePicker1.Value.ToShortDateString()
' date1 = (a.ToString + "-" + b.ToString).ToString
P_DATE = (Year(Me.DateTimePicker1.Value.ToShortDateString()).ToString + "-" + Month(Me.DateTimePicker1.Value.ToShortDateString()).ToString).ToString
Return P_DATE
End Function
[解决办法]
GROUP BY format(T_结账单.F_日期,'yyyy-MM') 不就行了吗？
