SELECT * FROM 表名 a WHERE (值1 IN (SELECT 值1 FROM 表名 GROUP BY 值1 HAVING COUNT(*) > 1)) and id not in (select min(id) from 表名 where 值1=a.值1)
对,如果ID值不连续的话,则显示ID值最小的那条记录
我刚才说错了,filebat(Mark) 说的对。是如果ID不连续话,则不显示该段的第一条记录
--测试数据 create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(10)) insert ta select 1, 2, '大', '多' union all select 2, 2, '大', '多' union all select 3, 2, '大', '多' union all select 4, 2, '大', '多'union all select 10, 3, '5', '92' union all select 11, 3, '5', '92' union all select 12, 3, '5', '92'union all select 100, 86, '34', '234' union all select 101, 86, '34', '234' --查询 select * from ta as tt where exists(select 1 from ta where tt.id=id+1) --清除 drop table ta
生成测试数据 create table aa ( ID int, 值1 varchar(20), 值2 varchar(20), 值3 varchar(20) )insert aa select 1,'2','大','多' union all select 2,'2','大','多' union all select 3,'2','大','多' union all select 4,'2','大','多' union all select 10,'3','5','92' union all select 11,'3','5','92' union all select 12,'3','5','92' 执行语句 select * from aa where aa.id not in (select min(id) from aa group by 值1,值2,值3 )善后 drop table aa
filebat(Mark) 的意思好像也得建个临时的表吧,
--楼主,希望真实的删除记录吗? --那样的话,就应该用一下临时表(或者表变量了) --测试数据 create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(10)) insert ta select 1, 2, '大', '多' union all select 2, 2, '大', '多' union all select 3, 2, '大', '多' union all select 4, 2, '大', '多' union all select 10, 3, '5', '92' union all select 11, 3, '5', '92' union all select 12, 3, '5', '92' union all select 100, 86, '34', '234' union all select 101, 86, '34', '234' --查询 declare @t table(id int, 值1 int, 值2 varchar(10), 值3 varchar(10)) insert @t select * from ta as tt where exists(select 1 from ta where tt.id=id+1)delete ta insert ta select * from @tselect* from ta --清除 drop table ta
filebat(Mark) ,谢谢你,也谢谢大家的热心帮助!谢谢了!!:)结帖
--测试数据 create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(20)) insert ta select 1, 2, '大', '多' union all select 2, 2, '大', '多' union all select 3, 2, '大', '多' union all select 4, 2, '大', '多' union all select 10, 3, '5', '92' union all select 11, 3, '5', '92' union all select 12, 3, '5', '92' union all select 100, 86, '34', '234' union all select 101, 86, '34', '234' --查询 insert ta select id, 值1, 值2, 值3+'@#$%^&*~' from ta as tt where exists(select 1 from ta where tt.id=id+1)delete ta where charindex('@#$%^&*~', 值3, 1)=0update ta set 值3=replace(值3, '@#$%^&*~', '')select* from ta --清除 drop table ta
是不是说如果ID不连续话,则不显示该段的第一条记录?
create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(10))
insert ta select 1, 2, '大', '多'
union all select 2, 2, '大', '多'
union all select 3, 2, '大', '多'
union all select 4, 2, '大', '多'union all select 10, 3, '5', '92'
union all select 11, 3, '5', '92'
union all select 12, 3, '5', '92'union all select 100, 86, '34', '234'
union all select 101, 86, '34', '234'
--查询
select *
from ta as tt
where exists(select 1 from ta where tt.id=id+1)
--清除
drop table ta
create table aa
(
ID int,
值1 varchar(20),
值2 varchar(20),
值3 varchar(20)
)insert aa
select 1,'2','大','多'
union all
select 2,'2','大','多'
union all
select 3,'2','大','多'
union all
select 4,'2','大','多'
union all
select 10,'3','5','92'
union all
select 11,'3','5','92'
union all
select 12,'3','5','92'
执行语句
select * from aa where aa.id not in (select min(id) from aa group by 值1,值2,值3 )善后
drop table aa
--那样的话,就应该用一下临时表(或者表变量了)
--测试数据
create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(10))
insert ta select 1, 2, '大', '多'
union all select 2, 2, '大', '多'
union all select 3, 2, '大', '多'
union all select 4, 2, '大', '多'
union all select 10, 3, '5', '92'
union all select 11, 3, '5', '92'
union all select 12, 3, '5', '92'
union all select 100, 86, '34', '234'
union all select 101, 86, '34', '234'
--查询
declare @t table(id int, 值1 int, 值2 varchar(10), 值3 varchar(10))
insert @t
select * from ta as tt
where exists(select 1 from ta where tt.id=id+1)delete ta
insert ta select * from @tselect* from ta
--清除
drop table ta
create table ta(id int, 值1 int, 值2 varchar(10), 值3 varchar(20))
insert ta select 1, 2, '大', '多'
union all select 2, 2, '大', '多'
union all select 3, 2, '大', '多'
union all select 4, 2, '大', '多'
union all select 10, 3, '5', '92'
union all select 11, 3, '5', '92'
union all select 12, 3, '5', '92'
union all select 100, 86, '34', '234'
union all select 101, 86, '34', '234'
--查询
insert ta
select id, 值1, 值2, 值3+'@#$%^&*~'
from ta as tt
where exists(select 1 from ta where tt.id=id+1)delete ta where charindex('@#$%^&*~', 值3, 1)=0update ta set 值3=replace(值3, '@#$%^&*~', '')select* from ta
--清除
drop table ta