老问题,再次求助.删除表里重复记录,保留最新的一条.ID.自增.
表结构如下.
表名:clientlog
列名:id(自增),buyongyuanyin,username,其它省略..
查询语句如下.不会错:
select * from clientlog where id not in
(select max(id) from clientlog group by username having count(username)>1)
and id in (select id from clientlog group by username
having count(username)>1);
这样会得到想要的结果.
但删除会爆错:
update clientlog set buyongyuanyin='aaa' where id not in(select max(id) from clientlog group by username
having count(username)>1) and id in (select id from clientlog group by username having count(username)>1);
出错如下:You can't specify target table 'clientlog' for update in FROM clause
望高手指点下,谢谢.
表结构如下.
表名:clientlog
列名:id(自增),buyongyuanyin,username,其它省略..
查询语句如下.不会错:
select * from clientlog where id not in
(select max(id) from clientlog group by username having count(username)>1)
and id in (select id from clientlog group by username
having count(username)>1);
这样会得到想要的结果.
但删除会爆错:
update clientlog set buyongyuanyin='aaa' where id not in(select max(id) from clientlog group by username
having count(username)>1) and id in (select id from clientlog group by username having count(username)>1);
出错如下:You can't specify target table 'clientlog' for update in FROM clause
望高手指点下,谢谢.
where exists
( select 1 from
(select username, max(id) id from clientlog group by username having count(username)>1)
a where clientlog.username=a.username and clientlog.id!=a.id
)
又不是彻底删除。。
use pubs
--建表
if object_id('clientlog') is not null drop table clientlog create table clientlog
(
[id] int identity(1,1) not null,
[buyongyuanyin] varchar(100) null,
[username] varchar(20) null
)
go--插入数据
insert into clientlog
select '','abert' union all
select '','abert' union all
select '','abert' union all
select '','Lee' union all
select '','Lee' union all
select '','Lee' union all
select '','Megers' union all
select '','Lucy' --查看
select * from clientlog
--查看结果
id buyongyuanyin username
1 abert
2 abert
3 abert
4 Lee
5 Lee
6 Lee
7 Megers
8 Lucy
--楼主代码（我用2000，修改id为username通过运行）
select * from clientlog where
id not in
(select max(id) from clientlog group by username having count(username)>1)
and
username in (select username from clientlog group by username
having count(username)>1)
--楼主代码查询结果
1 abert
2 abert
4 Lee
5 Lee--执行楼主代码进行更新（我用2000，修改id为username通过运行），不知道是不是楼主想要的结果？
update clientlog set buyongyuanyin='aaa'
where
id not in(select max(id) from clientlog group by username
having count(username)>1) and username in (select username from clientlog group by username having count(username)>1)
--（所影响的行数为 4 行）
1 aaa abert
2 aaa abert
3 abert
4 aaa Lee
5 aaa Lee
6 Lee
7 Megers
8 Lucy--2楼代码执行（删除改为查询）
--delete clientlog
select * from clientlog
where exists
( select 1 from
(select username, max(id) id from clientlog group by username having count(username)>1)
a where clientlog.username=a.username and clientlog.id!=a.id
)
--2楼代码执行结果（删除改为查询）
1 abert
2 abert
4 Lee
5 Lee--3楼代码执行（更新改为查询）
--update a
--set buyongyuanyin='aaa'
select *
from clientlog a where not exists(select 1 from clientlog where username=a.username and ID<a.ID)--3楼代码执行结果（更新改为查询），貌似条件有误，嘿嘿
1 abert
4 Lee
7 Megers
8 Lucy
drop table tb
go
create table tb
(
id int identity(1,1) primary key,
name varchar(20)
)
insert into tb(name)
select 'zyw' union all
select 'zyw' union all
select 'zyw1' union all
select 'zyw1' union all
select 'zyw1' union all
select 'zyw1' union all
select 'zyw2' union all
select 'zyw2' union all
select 'zyw2' select * from tb t where not exists (select * from tb where name=t.name and id>t.id)
go
declare @clientlog table
(
[id] int identity(1,1) not null,
[buyongyuanyin] varchar(100) null,
[username] varchar(20) null
)
--插入数据
insert into @clientlog
select '','abert' union all
select '','abert' union all
select '','abert' union all
select '','Lee' union all
select '','Lee' union all
select '','Lee' union all
select '','Megers' union all
select '','Lucy' select * from @clientlogMerge @clientlog as target
using (
select max(id),[username]
from @clientlog
group by [username]
)
as target_max(id,username)
on target.id = target_max.id
when matched then update set target.[username] = 'aaa'
when not matched BY TARGET THEN INSERT ([buyongyuanyin],[username]) VALUES (1, 1);select * from @clientlog/*
(8 行受影响)
id buyongyuanyin username
----------- ---------------------------------------------------------------------------------------------------- --------------------
1 abert
2 abert
3 abert
4 Lee
5 Lee
6 Lee
7 Megers
8 Lucy(8 行受影响)(4 行受影响)id buyongyuanyin username
----------- ---------------------------------------------------------------------------------------------------- --------------------
1 abert
2 abert
3 aaa
4 Lee
5 Lee
6 aaa
7 aaa
8 aaa(8 行受影响)
*/
我用的也是MYSQL,但我的SQL就是报错.
说这个表不能修改和删除.这是一个单独的表,没有外键关系.但修改或者删除,如果你指定一个ID.如删除ID=1的数据.就不会出错.但我用我写的SQL就出错.
请指点下.
update a
set buyongyuanyin='aaa'
select * from clientlog a where id =(select max(id) from clientlog group by username having count(username)>1)
出现如下错误:
ERROR 1064 (42000): 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 'exist
s
( select 1 from
(select username, max(id) id from clientlog grou' at line 1注:反括号是打上的.
select id from clientlog group by username having count(username)>1
然后再其中找出最大的.
select max(id) from clientlog group by username having count(username)>1
最后在修改或删除.
update clientlog set buyongyuanyin='aaa' where id not in(select max(id) from clientlog group by username
having count(username)>1) and id in (select id from clientlog group by username having count(username)>1);
这样思路有没有错.并且语法没有错.但为什么告诉我说不能改变或删除这张表.这表是非常纯洁的一张表.和别的表没有关系.