我有一张表的记录如下:
col1 col2 col3
111 1 adsf
111 2 sdfd
111 3 tyuio
112 1 dfgg
222 1 sdfg
222 2 vbmv
222 3 tuyi
200 4 dfhfgh
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ppp在上表中将col1,col2相同的找去的记录为
111 1 adsf
111 2 sdfd
111 3 tyuio
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ppp并将上面的重复记录修改,如重复一次,在col1后加A,再重复加B,再重复加C....
最后如下
111 1 adsf
111 2 sdfd
111 3 tyuio
222 1 sdfg
222 2 vbmv
222 3 tuyi
111A 1 ert
111A 2 hjkj
222A 1 sdfg
222A 2 vbmv
222A 3 tuyi
111B 1 ppp
请问这个怎么做??
col1 col2 col3
111 1 adsf
111 2 sdfd
111 3 tyuio
112 1 dfgg
222 1 sdfg
222 2 vbmv
222 3 tuyi
200 4 dfhfgh
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ppp在上表中将col1,col2相同的找去的记录为
111 1 adsf
111 2 sdfd
111 3 tyuio
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ert
111 2 hjkj
222 1 sdfg
222 2 vbmv
222 3 tuyi
111 1 ppp并将上面的重复记录修改,如重复一次,在col1后加A,再重复加B,再重复加C....
最后如下
111 1 adsf
111 2 sdfd
111 3 tyuio
222 1 sdfg
222 2 vbmv
222 3 tuyi
111A 1 ert
111A 2 hjkj
222A 1 sdfg
222A 2 vbmv
222A 3 tuyi
111B 1 ppp
请问这个怎么做??
解决方案 »
- varchar转换成smalldatetime
- 求助,本人菜鸟
- SQL的SP4安装问题,急!
- 字符串查询
- SQL2005关于联接查询的问题
- 请详细解释一下这个交叉表sql语句的意思
- 求一条sql语句!
- System.Data.SqlClient.SqlError: 媒体集有 2 个媒体簇,但只提供了 1 个。必须提供所有成员。 (Microsoft.SqlServer.Smo)
- 歡迎大家討論SQL Server 的Ceiling函數
- phpmyadmin mqsl 获取当前时间问题
- 向大峡们求一sql语句,急在线等!!!
- 请问@@identity怎么用啊?insert into usertable (username) values ('dddd') select @@identity
set col1 = col1 + char((select count(1) from tablename where col1 = a.col1 and col2 = a.col2 and col3 < col3) + ascii('A') - 1)
from tablename a
update a
set col1 = col1 + char((select count(1) from tablename where col1 = a.col1 and col2 = a.col2 and col3 < col3) + ascii('A') - 1)
from tablename a
where exists(select 1 from tablename where col1 = a.col1 and col2 = a.col2 and col3 < col3)
update a
set col1 = col1 + char((select count(1) from tablename where col1 = a.col1 and col2 = a.col2 and col3 < a.col3) + ascii('A') - 1)
from tablename a
where exists(select 1 from tablename where col1 = a.col1 and col2 = a.col2 and col3 < a.col3)
select col1,col2,col3 from tablename
group by col1 having count(col1)>=2更新:
update a
set col1 = col1 + char((select count(1) from tablename where col1 = a.col1 and col2 = a.col2 and col3 < a.col3) + ascii('A') - 1)
from tablename a
where exists(select 1 from tablename where col1 = a.col1 and col2 = a.col2 and col3 < a.col3)
set col1 = col1 +"A"
group by col1 having count(col1)=2uodate a
set col1 = col1 +"B"
group by col1 having count(col1)=3uodate a
set col1 = col1 +"C"
group by col1 having count(col1)=4上面有错的话,我就没办法了。
--建立测试环境
create table #TestA(col1 varchar(10),col2 int,col3 varchar(20))
insert into #TestA
select '111',1,'adsf' union all
select '111',2,'sdfd' union all
select '111',3,'tyuio' union all
select '112',1,'dfgg' union all
select '222',1,'sdfg' union all
select '222',2,'vbmv' union all
select '222',3,'tuyi' union all
select '200',4,'dfhfgh' union all
select '111',1,'ert' union all
select '111',2,'hjkj' union all
select '222',1,'sdfg' union all
select '222',2,'vbmv' union all
select '222',3,'tuyi' union all
select '111',1,'ppp'--测试
--建立一个字符串的虚拟表,用于特殊处理
select top 26 identity(int,65,1) AsciiID
into #AsciiID
from sysobjects A,sysobjects B
--建立一个自动增量的复本
select *,identity(int,1,1) TID into #testAA from #testA
--按递增取得重复值的第几次
select *,(select count(*)
from #testAA B
where A.col1=B.col1 and A.col2=B.col2 and B.TID<=A.TID
group by B.col1,B.col2
) DoubleTime
from #testAA A
--进行测试查询
select C.col1,C.col2,C.col1+isnull(D.AsciiChar,'') NewCol1
from
(
select *,(
select count(*)
from #testAA B
where A.col1=B.col1 and A.col2=B.col2 and B.TID<=A.TID
group by B.col1,B.col2
) DoubleTime
from #testAA A) C
left join
(
select char(AsciiID) AsciiChar,AsciiID-63 TID
from #AsciiID
) D
on C.DoubleTime=D.TID
--order by C.col1
--显示结果
/*
Col1 Col2 NewCol1
111 1 111
111 2 111
111 3 111
112 1 112
222 1 222
222 2 222
222 3 222
200 4 200
111 1 111A
111 2 111A
222 1 222A
222 2 222A
222 3 222A
111 1 111B*/
--删除测试环境
drop table #testA
drop table #testAA
drop table #AsciiID
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