1删除一个表中除id号不同 其它项都相同的行 id为自增型
2找出未选“数学”的学生 -----三个表(学生,学生课程,课程)
2找出未选“数学”的学生 -----三个表(学生,学生课程,课程)
解决方案 »
- 请问各位大侠,如何修改排序号?
- 这个SQL语句我觉得可以简化,但就是写不出来
- sql2000有像MS access这样的工具吗?
- sql2000数据库文件(或bak备份文件)直接在sql2005中还原恢复,有没有问题?
- 大家帮忙看一下这条sql语句。
- 执行 exec sp_fulltext_database N'enable' 语句时出现"未安装全文检索或未能装载某一全文组件。"错误信息,要安装什么组件?
- 关于编码问题SQL
- GROUP BY 是怎么用的请教
- 请问对text字段的WHERE判断,用LIKE快还是用CHARINDEX快??
- 关于存储过程的问题
- 关于ASP分批读入SQL的问题,请教专家!!~~~
- 有没有专门编辑SQL的编辑器,能格式化,智能提示,语法高亮等。
方法1:
--简单的方法就是借用临时表
--方式:把数据首先放到临时表
--在临时表中处理重复记录问题.
--删除物理数据表
--从临时表把数据取出来,放入物理表中
--删除临时表
Select distinct * into #temp from table1delete table1insert into table1
Select * from #temp drop table #temp方法2:
--方式2:
delete 表名
from 表名 tt
where exists(select 1 from 表名 where 字段=tt.字段 and 字段=tt.字段 and 主键<tt.主键)
方法3
--保留最小的ID
delete 表 where ID not in(select min(ID) from 表 group by 字段...(注:重复的字段行))
思路:
select distinct S.*
from
学生 S,学生课程 SK,课程 K
where
S.学生ID =SK.学生ID and K.课程ID=SK.课程ID and K.课程Name<>'数学'
(
id int IDENTITY (1,1)NOT NULL,
mail varchar(100)
)insert A select '[email protected]'
insert A select '[email protected]'
insert A select '[email protected]'
insert A select '[email protected]'
insert A select '[email protected]'
delete A from A where mail in(select mail from A group by mail having count(1)>1)select * from A
insert into tb select 'a','b'
insert into tb select 'v','b'
insert into tb select 'a','b'
insert into tb select 'c','d'
insert into tb select 'e','f'select * from tbdelete tb where id in(select id from tb a where exists(select * from tb where a=a.a and b=a.b and id<a.id))select * from tbdrop table tb
(
id int identity(1,1),
name varchar(10)
)
insert @tb
select 'bb' union all
select 'cc' union all
select 'bb'
delete @tb where id in(select id from @tb a where exists(select * from @tb where name=a.name and id<a.id))
select * from @tb/*
测试结果
id name
----------- ----------
1 bb
2 cc
*/
delete from tb where exists(select 1 from tb tb1 where id<>tb1.id and id1=tb1.id1)
假设id,id1两列
2.的看不太明白
insert into @tb select 1,3
union all select 2,4
union all select 3,3
delete from @tb where id in(select id from @tb tb where exists(select 1 from @tb tc where id<tb.id and id1=tb.id1))
select * from @tb
/*
id id1
1 3
2 4
*/
解决方法:
create table aa
(
id int primary key,
Name varchar(20) not null,
code varchar(200) not null
)
insert into aa values(1,'dsf','asfasdfasdf')
insert into aa values(2,'dsf1','asfasdfasdf')
insert into aa values(3,'dsf2','asfasdfasdf')
insert into aa values(4,'dsf1','asfasdfasdf')
insert into aa values(5,'dsf3','asfasdfasdf')
insert into aa values(6,'dsf','asfasdfasdf')
insert into aa values(7,'dsf4','asfasdfasdf')
insert into aa values(8,'dsf','asfasdfasdf')
insert into aa values(9,'dsf4','asfasdfasdf')
insert into aa values(10,'dsf','asfasdfasdf')
insert into aa values(11,'dsf9','asfasdfasdf')
insert into aa values(12,'dsf','asfasdfasdf')
insert into aa values(13,'dsf9','asfasdfasdf')delete aa where id in(select id from aa a1 where exists(select * from aa where Name=a1.Name and code=a1.code and id<a1.id))select Name,code from aa
原理:就是从删除后面数据中的数据中查找重复的记录,然后删除
用到了exists方法,请注意多多理解!create table student
(
id int primary key,
Name varchar(20) not null
)
create table course
(
id int primary key,
Name varchar(20) not null
)
create table sc
(
stuid int ,
courid int
)insert into student values(1,'刘某')
insert into student values(2,'潘某')
insert into student values(3,'温某')insert into course values(1,'数学')
insert into course values(2,'语文')
insert into course values(3,'英语')insert into sc values(1,1)
insert into sc values(2,2)
insert into sc values(3,3)select student.Name from student where id not in (select stuid from sc,course where sc.courid = course.id and course.Name = '数学')自己到查询分析器里看一下吧
我试验过是可以的 桂林电子科技大学计算机系
信息管理与信息系统
2006.3.29
delete aa where id in(select id from aa a1 where exists(select * from aa where Name=a1.Name and code=a1.code and id!=a1.id))
改为不等于,更好