共10 条记录, 每页显示 10 条, 页签: [1]

[浏览完整版]

标题：MYSQL用帮助SQL出错

1楼

lin98 发表于：2023/11/6 10:53:00

帮助：

'Dim cmd As New SQ-LCommand

'cmd.C

'Dim dt As DataTable

'Dim Cols1() As String = {"来源列一", "来源列二", "来源列三"}

'Dim Cols2() As String = {"接收列一", "接收列二", "接收列三"}

'cmd.CommandText = "SELEC-T * From {表A} Where 条件语句"

'dt = cmd.ExecuteReader()

'For Each dr1 As DataRow In dt.DataRows

' Dim dr2 As DataRow = DataTables("表B").AddNew()

' For i As Integer = 0 To Cols1.Length - 1

' dr2(Cols2(i)) = dr1(Cols1(i))

' Next

'Next

MYSQ-L

'Dim db = HySq-l.DataBaseFactory.CreateDatabase("my")

'Dim Sql = "Selec-t 产品编号, max(日期) as 日期 From {kcxb_tb} group by 产品编号"

'Dim Cols1() As String = {"来源列一", "来源列二", "来源列三"}

'Dim Cols2() As String = {"来源列一", "来源列二", "来源列三"}

'Dim dt As system.data.DataTable = db.ExecuteDataSet(Sq-l).Tables(0)

'dt.TableName = "kcb_tb"

'For Each dr1 As DataRow In dt.Rows

' Dim dr2 As DataRow = DataTables("登记窗口_Table1").AddNew()

' For i As Integer = 0 To Cols1.Length - 1

' dr2(Cols2(i)) = dr1(Cols1(i))

' Next

'Next

报错提：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '} group by 产品编号' at line 1

2楼

有点蓝 发表于：2023/11/6 10:54:00

使用外部数据源最好都去掉表名的大括号

3楼

lin98 发表于：2023/11/6 12:34:00

'Dim db = HySq-l.DataBaseFactory.CreateDatabase("my")

'Dim Sql = "Selec-t 产品编号, max(日期) as 日期 From kcxb_tb group by 产品编号"

'Dim Cols1() As String = {"来源列一", "来源列二", "来源列三"}

'Dim Cols2() As String = {"来源列一", "来源列二", "来源列三"}

'Dim dt As system.data.DataTable = db.ExecuteDataSet(Sq-l).Tables(0)

'dt.TableName = "kcb_tb"

'For Each dr1 As DataRow In dt.Rows

' Dim dr2 As DataRow = DataTables("登记窗口_Table1").AddNew()

' For i As Integer = 0 To Cols1.Length - 1

' dr2(Cols2(i)) = dr1(Cols1(i))

' Next

'Next

报错：

无法将类型为“System.Data.DataRow”的对象强制转换为类型“Foxtable.DataRow”。

4楼

有点蓝 发表于：2023/11/6 13:39:00

For Each dr1 As System.Data.DataRow In dt.Rows

5楼

lin98 发表于：2023/11/6 16:12:00

'Dim f As New Filler

'f.SourceTable = DataTables("登记窗口_Table2") '

'f.SourceCols = "产品编号" ,"存量"'

'f.DataTable = DataTables("登记窗口_Table1") '

'f.DataCols = "产品编号","数量" '

'f.Fill() '

'Dim Sql = "Selec-t 产品编号, max(日期) as 日期 From kcxb_tb group by 产品编号"

上面代码，如何实现从Table2按不重复产品编号其最后一条的值（数量），写入Table1里？

6楼

有点蓝 发表于：2023/11/6 16:24:00

Filler做不到。换种方式，使用getvalues去获取不重复的编号，然后遍历不重复的编号查询日期最大的记录

7楼

lin98 发表于：2023/11/10 9:24:00

'Dim db = HySq-l.DataBaseFactory.CreateDatabase("my")

'Dim Sql = "Selec-t 产品编号, max(日期) as 日期 From kcxb_tb group by 产品编号"

'Dim Cols1() As String = {"来源列一", "来源列二", "来源列三"}

'Dim Cols2() As String = {"来源列一", "来源列二", "来源列三"}

'Dim dt As system.data.DataTable = db.ExecuteDataSet(Sq-l).Tables(0)

'dt.TableName = "kcb_tb"

' For Each dr1 As System.Data.DataRow In dt.Rows

' Dim dr2 As DataRow = DataTables("登记窗口_Table1").AddNew()

' For i As Integer = 0 To Cols1.Length - 1

' dr2(Cols2(i)) = dr1(Cols1(i))

' Next

'Next

执行报错：

列“统计_数量”不属于表 xb_tb。

资料：MYSQL

主表的字段：日期，产品，数量

明细表的字段：日期，产品，统计_数量

需求：实现主表取明细表不重复，最后记录的数据或最后日期的数据，如何实现？

8楼

有点蓝 发表于：2023/11/10 9:54:00

提示很明显了，列名不正确

9楼

lin98 发表于：2023/11/10 10:07:00

排查字段，没有问题，放弃上面方法

实现下面需求，MYSQ-L，有其他代码？

需求：实现主表取明细表不重复，最后记录的数据或最后日期的数据，如何实现？

注：明细表的数据量比较大，

[此贴子已经被作者于2023/11/10 10:08:31编辑过]

10楼

有点蓝 发表于：2023/11/10 10:15:00

SqlServer可以这样

Select a.* From kcxb_tb as a inner join (Select 产品编号, max(日期) as 日期 From kcxb_tb group by 产品编号) as b on a. 产品编号=b. 产品编号 and a.日期=b.日期

至于mysql的语法我也没用过，就不清楚了，自己上网搜搜

共10 条记录, 每页显示 10 条, 页签: [1]