『壹』 mysql 怎麼通過sql語句如何批量去掉某一個表中某一個欄位的下面的相同部分字元串。
建議你用正則匹配
『貳』 SQL語句求助：批量刪除具有相同前綴的表
在phpmyadmin中先運行（假設前綴是"cdb_"）：
select concat('drop table ', table_name, ';')
from information_schema.tables;
where table_name like 'cdb_%'
然後把執行的結果從網頁中復制出來，粘貼到記事本中，把記事本中的文件另存為：droptable.sql 。（文件類型中選「所有文件」）
在phpmyadmin中，頂部的菜單中，有「import」，點擊後，你把新建的文件上傳上去，然後點擊右下方的「執行」
『叄』 sql如何刪除重復數據
sql查詢去除重復值語句
sql 單表/多表查詢去除重復記錄
單表distinct
多表group by
group by 必須放在 order by 和 limit之前，不然會報錯
************************************************************************************
1、查找表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、刪除表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷，只留有rowid最小的記錄
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多餘的重復記錄（多個欄位）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、刪除表中多餘的重復記錄（多個欄位），只留有rowid最小的記錄
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多餘的重復記錄（多個欄位），不包含rowid最小的記錄
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>
『肆』 sql server 中如何刪除相同名字的信息
直接給你代碼啦，--取重名
select * from table as t1 where exists(select 1 from table as t2 where t1.name = t2.name and t1.age <> t2.age)
--刪重名
delete from table as t1 where exists(select 1 from table as t2 where t1.name = t2.name and t1.age <> t2.age)
『伍』 怎麼使用SQL語句批量刪除多個表的相同欄位
在phpmyadmin中先運行（假設前綴是\"cdb_\"）：
select
concat(\'drop
table
\',
table_name,
\';\')
from
information_schema.tables;
where
table_name
like
\'cdb_%\'
然後把執行的結果從網頁中復制出來，粘貼到記事本中，把記事本中的文件另存為：droptable.sql
。（文件類型中選「所有文件」）
在phpmyadmin中，頂部的菜單中，有「import」，點擊後，你把新建的文件上傳上去，然後點擊右下方的「執行」
『陸』 如何在Sql中將重復的所有欄位刪除
用SQL語句,刪除掉重復項只保留一條
在幾千條記錄里,存在著些相同的記錄,如何能用SQL語句,刪除掉重復的呢
1、查找表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷
select
*
from
people
where
peopleId
in
(select
peopleId
from
people
group
by
peopleId
having
count(peopleId)
>
1)
2、刪除表中多餘的重復記錄，重復記錄是根據單個欄位（peopleId）來判斷，只留有rowid最小的記錄
delete
from
people
where
peopleName
in
(select
peopleName
from
people
group
by
peopleName
having
count(peopleName)
>
1)
and
peopleId
not
in
(select
min(peopleId)
from
people
group
by
peopleName
having
count(peopleName)>1)
3、查找表中多餘的重復記錄（多個欄位）
select
*
from
vitae
a
where
(a.peopleId,a.seq)
in
(select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count(*)
>
1)
4、刪除表中多餘的重復記錄（多個欄位），只留有rowid最小的記錄
delete
from
vitae
a
where
(a.peopleId,a.seq)
in
(select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count(*)
>
1)
and
rowid
not
in
(select
min(rowid)
from
vitae
group
by
peopleId,seq
having
count(*)>1)
5、查找表中多餘的重復記錄（多個欄位），不包含rowid最小的記錄
select
*
from
vitae
a
where
(a.peopleId,a.seq)
in
(select
peopleId,seq
from
vitae
group
by
peopleId,seq
having
count(*)
>
1)
and
rowid
not
in
(select
min(rowid)
from
vitae
group
by
peopleId,seq
having
count(*)>1)
6.消除一個欄位的左邊的第一位：
update
tableName
set
[Title]=Right([Title],(len([Title])-1))
where
Title
like
'村%'
7.消除一個欄位的右邊的第一位：
update
tableName
set
[Title]=left([Title],(len([Title])-1))
where
Title
like
'%村'
8.假刪除表中多餘的重復記錄（多個欄位），不包含rowid最小的記錄
update
vitae
set
ispass=-1
where
peopleId
in
(select
peopleId
from
vitae
group
by
peopleId
『柒』 用SQL如何批量刪除具有相同前綴的表sql批量刪除表
alter proc DeleteSingleTable(@tablename varchar(100))
as
begin
declare @SQL varchar(2000)
declare @constraintName varchar(100)
declare curName cursor for
select name from sysobjects
where xtype = 'f ' and parent_obj =
(select [id] from sysobjects where [name]=@tablename and xtype = 'u ')
open curName
fetch next from curName into @constraintName
while @@fetch_status = 0
begin
set @SQL = 'alter table ' + @tablename + ' drop constraint '
set @SQL = @SQL + @constraintName
exec(@SQL)
fetch next from curName into @constraintName
end
close curName
deallocate curName
end