我如何在保留所有列的同时，在新表的字段中选择SQL来区分? [英] How do I SQL Select distinct on a field into new table, while keeping all columns?

问题描述

我需要选择数据中的每个版本之一，同时还要保留所有列.然后，我需要将每个版本的一条记录导出到新的.dbf文件.我遇到的问题与SQL语句有关.我尝试了SQL的不同迭代 声明，但似乎没有任何作用.我提供的代码选择每个版本之一，但不包括其余各列.这是我的代码:

I need to select one of each version in the data while keeping all the columns as well. Then I need to export the one record of each version to a new .dbf file. The issue I am having is with the SQL statement. I have tried different iterations of the SQL Statement but nothing appears to work. The code I have provided selects one of each version but does not include the rest of the columns. Here is my code:

Dim ofd As New OpenFileDialog
        With ofd
            .Filter = "DBASE File (*.dbf)|*.dbf"
            .Multiselect = False
            .CheckFileExists = True
        End With

        If ofd.ShowDialog() = DialogResult.OK Then
            Dim fi As New IO.FileInfo(ofd.FileName)
            Dim cn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBase IV;Data Source='" _
                                                & fi.DirectoryName & "'")
            Dim TableName As String = fi.Name.Substring(0, fi.Name.Length - fi.Extension.Length)
            Dim cmd As New OleDb.OleDbCommand(TableName, cn)
            cmd.CommandType = CommandType.TableDirect

            cn.Open()
            Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
            dt.Load(rdr)

            SelectField.ShowDialog()


            Dim dBaseConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & path & ";" & "Extended Properties=dBase IV")

            dBaseConnection.Open()

            Dim SQLCreateCommand As String

            Dim sql2 = "SELECT DISTINCT " & Field1 & " INTO NewTable " & " from " & TableName

            Dim dBaseCommand As New System.Data.OleDb.OleDbCommand(sql2, dBaseConnection)

            dBaseCommand.ExecuteNonQuery()
            dBaseConnection.Close()

            cn.Close()
            cn.Dispose()
        End If

输入:

版本名字姓氏

A           鲍勃                            史密斯

A               Bob            Smith

A           杰里        黄金

A               Jerry           Gold

B           简                                   冬天

B               Jen              Winter

B           Hailey        史密斯

B               Hailey          Smith

C                                  约翰                            约翰逊

C              John            Johnson

输出:看看它是如何选择每个版本之一并包括每个列的，这就是我需要做的.

Output:  See how its selecting one of each version and including each column, that's what I need to do.

版本   名字姓氏

Version     Firstname   LastName

A           鲍勃                            史密斯

A               Bob            Smith

B           简                                   冬天

B               Jen              Winter

C                                 约翰         约翰逊

C              John            Johnson

推荐答案

尝试以下语法

SELECT SomeField INTO Destination FROM TableName GROUP BY SomeField

如果这不起作用，则问题在于OleDb类如何处理此问题，这意味着您可能需要使用带有DISTINCT的SELECT语句，然后将数据加载到DataTable中.接下来一次插入一行.

If that does not work then the issue is with how OleDb class is handling this which means you may need to so a SELECT statement with DISTINCT then load the data into a DataTable. Next insert one row at a time.

这篇关于我如何在保留所有列的同时，在新表的字段中选择SQL来区分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！