问题1:我想删除表1里EMAIL相同的记录,但是要留下一条。怎么做?
delete a
from 表1 a
where id<>(select top 1 id from 表1 where E_MAIL=a.E_MAIL order by id)问题2:想找出各科成绩都在80以上的学生的姓名。
select distinct 姓名
from 表2 a
where not exists (
select 1 from 表2
where 姓名=a.姓名
and 成绩<80
)
delete a
from 表1 a
where id<>(select top 1 id from 表1 where E_MAIL=a.E_MAIL order by id)问题2:想找出各科成绩都在80以上的学生的姓名。
select distinct 姓名
from 表2 a
where not exists (
select 1 from 表2
where 姓名=a.姓名
and 成绩<80
)
delete a
from 表1 a
where id<>(select top 1 id from 表1 where E_MAIL=a.E_MAIL order by id)select distinct 姓名
from 表2 a
where not exists (
select 1 from 表2
where 姓名=a.姓名
and 成绩<80
)
如果ID是唯一的,可以這麼寫Delete From 表1 Where ID Not In (Select Min(ID) From 表1 Group By E_MAIL)--或者Delete From 表1 Where ID Not In (Select Max(ID) From 表1 Group By E_MAIL)
---------------------------------------------------------------
select * from 表1 a where not exists (select * from 表1 where a.E_MAIL=E_MAIL and a.ID>ID)问题2:想找出各科成绩都在80以上的学生的姓名。
----------------------------------------------
select 姓名 from @t group by 姓名 having (sum(case when 成绩>80 then 1 else 0 end )>1)
ID E_MAIL 姓名 科目 成绩1 [email protected] 张三 语文 85
2 [email protected] 张三 数学 95
3 [email protected] 王五 语文 31
4 [email protected] 王五 数学 86
问题1:我想删除表1里EMAIL相同的记录,但是要留下一条。怎么做?
问题2:想找出各科成绩都在80以上的学生的姓名。
请各位大侠帮忙。1
delete 表1 where id not in (select max(id) id from 表1 group by e_mail)2select a.姓名 from
(
select 姓名 , count(*) cnt from 表2 group by 姓名
) a,
(
select 姓名 , count(*) cnt from 表2 where 成绩 >= 80 group by 姓名
) b
where a.姓名 = b.姓名 and a.cnt = b.cnt
where exists(select 1 from tab b where a.e_mail=b.e_mail and a.id<b.id)
where a.姓名 = b.姓名 and a.cnt = b.cnt
delete t
where id <>(select min(id) from t group by e_mail order by id)2:select * from t a where not exists(select 1 from t where name=t.name and chenji<80)
delete t a
where not exists(select 1 from t where a.e_mail=e_mail and id<a.id)
问题1:我想删除表1里EMAIL相同的记录,但是要留下一条。怎么做?
---------------------------------------------------------------
delete from @t where id not in (select max(id)from @t group by num)
问题2:想找出各科成绩都在80以上的学生的姓名。
----------------------------------------------
select 姓名 from @t group by 姓名 having (sum(case when 成绩>80 then 1 else 0 end )>1)
declare @t table(id int, num int)
insert into @t
select 1,1
union all select 2,1
union all select 3,2
union all select 4,2
union all select 5,3select * from @t--查看表
delete from @t where id not in (select max(id)from @t group by num)select * from @t--查看结果
select * from 表1 where id in(
select min(id) from 表1 group by E_MAIL)
问题2:想找出各科成绩都在80以上的学生的姓名。
select * from 表2 where 姓名 in(
select 姓名 from 表2 having min(成绩)>=80 group by 姓名)