Question

SQL问题 在线等 急！！！！！！！！

一个表中数据如下:
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 引入子查询时，在选择列表中只能指定一个表达式。 这样的错误

Answer 1

DELETE FROM biao
WHERE (num NOT IN
(SELECT MIN(num)
FROM biao
GROUP BY name))

经过测试的，应该没问题

Answer 2

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)
这么写就好了

Answer 3

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)

Answer 4

一楼的应该不对。

Answer 5

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
)

Answer 6

delete from table_name where num not in(select min(num) from table_name group by name) ,这样写，你一个字段当然匹配一个选择列了，选择列里可以没有分组的列