我有一张表的记录如下:
col1 col2 col3
111 1 adsf
111 2 sdfd
112 1 dfgg
222 1 sdfg
222 2 vbmv
200 4 dfhfgh
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
111 1 ppp
111 2 fghj
111 1 jhlj我希望将上面的重复记录修改,如重复一次,在col1后加A,再重复加B,再重复加C....
最后如下
111 1 adsf
111 2 sdfd
112 1 dfgg
222 1 sdfg
222 2 vbmv
200 4 dfhfgh
111A 1 ert
111A 2 hjkj
222A 1 sdfg
222A 2 vbmv
111B 1 ppp
111B 2 fghj
111C 1 jhlj
请问这个怎么做??
col1 col2 col3
111 1 adsf
111 2 sdfd
112 1 dfgg
222 1 sdfg
222 2 vbmv
200 4 dfhfgh
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
111 1 ppp
111 2 fghj
111 1 jhlj我希望将上面的重复记录修改,如重复一次,在col1后加A,再重复加B,再重复加C....
最后如下
111 1 adsf
111 2 sdfd
112 1 dfgg
222 1 sdfg
222 2 vbmv
200 4 dfhfgh
111A 1 ert
111A 2 hjkj
222A 1 sdfg
222A 2 vbmv
111B 1 ppp
111B 2 fghj
111C 1 jhlj
请问这个怎么做??
as
set col1 = col+A where col2 =1
set col1 = col+B where col2 =2
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim tablename As String = TextBox1.Text.Trim
Dim column1 As String = TextBox2.Text.Trim
Dim column2 As String = TextBox3.Text.Trim
Dim column3 As String = TextBox4.Text.Trim
Dim conn As New SqlConnection()
conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstr")
conn.Open()
Dim cmd As New SqlCommand()
cmd.Connection = conn
Dim str1 As String
str1 = "select " + column1 + ", " + column2 + ", Count(" + column1 + "+" + column2 + ") as c from " + tablename + " group by " + column1 + ", " + column2 + " having Count(" + column1 + "+" + column2 + ")>1"
'MessageBox.Show(str1)
cmd.CommandText = str1
Dim col1(), col2() As String
Dim m As Integer
Try Dim reader As SqlDataReader = cmd.ExecuteReader
While reader.Read
ReDim Preserve col1(m)
ReDim Preserve col2(m)
col1(m) = reader(0)
'MessageBox.Show(col1(m))
col2(m) = reader(1)
'MessageBox.Show(col2(m))
m = m + 1
End While reader.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Dim i As Integer
For i = 0 To col1.Length - 1
Dim str2 As String
str2 = "select " + column1 + "," + column2 + "," + column3 + " from " + tablename + " where " + column1 + "='" + col1(i).ToString + "' and " + column2 + "='" + col2(i).ToString + "'"
'MessageBox.Show(str2)
cmd.CommandText = str2
Dim newcol1(), newcol2(), newcol3() As String
Dim n As Integer = 0
Dim reader2 As SqlDataReader = cmd.ExecuteReader
While reader2.Read
ReDim Preserve newcol1(n)
ReDim Preserve newcol2(n)
ReDim Preserve newcol3(n)
newcol1(n) = reader2(0)
'MessageBox.Show(newcol1(n))
newcol2(n) = reader2(1)
'MessageBox.Show(newcol2(n))
newcol3(n) = reader2(2)
'MessageBox.Show(newcol3(n))
n = n + 1
End While
reader2.Close() Try Dim k As Integer = 0
For k = 0 To newcol3.Length - 2
Dim upstr As String
'upstr = newcol1(k).ToString + "s" + k.ToString
'upstr = newcol1(k).ToString + "s" + k.ToString
upstr = newcol1(k).ToString + "char(ascii('A'-1))"
Dim str3 As String
str3 = "update a"
str3 = str3 + " set " + column1 + " = " + column1 + " + char((select count(1) from " + tablename + " where " + column1 + "='" + newcol1(k) + "' and " + column2 + "='" + newcol2(k) + "' and " + column3 + "='" + newcol3(k) + "') + ascii('A') - 1)"
str3 = str3 + " from " + tablename + " a where " + column1 + "='" + newcol1(k) + "' and " + column2 + "='" + newcol2(k) + "' and " + column3 + "='" + newcol3(k) + "'"
'str3 = "update dbo.x_test0 set col1='" + upstr + "' where col1='" + newcol1(k) + "' and col2='" + newcol2(k) + "' and col3='" + newcol3(k) + "'"
'str3 = "update test set " + column1 + "='" + upstr + "' where " + column1 + "='" + newcol1(k) + "' and " + column2 + "='" + newcol2(k) + "' and " + column3 + "='" + newcol3(k) + "'"
'MessageBox.Show(str3)
cmd.CommandText = str3
cmd.ExecuteNonQuery()
'upstr = upstr + "s"
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Next
conn.Close()
MessageBox.Show("ok")
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim tablename As String = TextBox1.Text.Trim
Dim column1 As String = TextBox2.Text.Trim
Dim column2 As String = TextBox3.Text.Trim
Dim column3 As String = TextBox4.Text.Trim
Dim conn As New SqlConnection()
conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("connstr")
conn.Open()
Dim cmd As New SqlCommand()
cmd.Connection = conn
Dim str1 As String
str1 = "select count(*) from " + tablename + " a, (select " + column1 + ", " + column2 + " from " + tablename + " group by " + column1 + ", " + column2 + " having count(*) > 1) b "
str1 = str1 + "where(a." + column1 + " = b." + column1 + " And a." + column2 + " = b." + column2 + ")"
cmd.CommandText = str1
'MessageBox.Show(str1)
Dim result As String
result = cmd.ExecuteScalar()
If Int(result) > 0 Then
MessageBox.Show("还有" + result + "条重复记录", "提示")
Else
MessageBox.Show("无重复记录", "提示成功")
End If
conn.Close()
End Sub