SQL问题 在线等 急!!!!!!!!
来源:百度知道 编辑:UC知道 时间:2024/07/05 16:16:20
num name
1 a
2 b
3 b
4 c
5 a
6 c
现在写一条delete语句 去掉name字段相同的,并且保留 num字段最小的
删除如下面结果:
num name
1 a
2 b
4 c
怎么写SQL 在线等 急!!!!!!!!
一楼的,我也这样写过,不对 会报
消息 116,级别 16,状态 1,第 1 行
当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式。 这样的错误
DELETE FROM biao
WHERE (num NOT IN
(SELECT MIN(num)
FROM biao
GROUP BY name))
经过测试的,应该没问题
delete from table_name where num not in(select min(num) num,name from table_name group by name)
------------补充---------------
刚才写错了
delete from kiss where num not in(select min(num) num from kiss group by name)
这么写就好了
delete from table1 x where exists (select 1 from
(select name,min(num) num from table1 where name in (select name from (select name,count(*) cnt from table1 group by name having count(*)>1)) where name=x.name and num=x.num)
一楼的应该不对。
delete from table
where num in(
select distinct(t1.num)
from table t1
left join table t2
on t1.name = t2.name
) and not in(
select min(distinct(t1.num))
from table t3
left join table t4
on t3.name = t4.name
group by name
)
delete from table_name where num not in(s