INFO表如下:
type name age
1 a 13
1 b 14
1 b 13
无主键的表,想把第3条记录删除,SQL语句如下:
DELETE FROM INFO
WHERE (age NOT IN(select max(age) from INFO group by type,name))
不知道为什么,删不掉它!
如果把NOT IN改为IN,会把所有数据都删掉
请大家看下问题出在哪里啊?
type name age
1 a 13
1 b 14
1 b 13
无主键的表,想把第3条记录删除,SQL语句如下:
DELETE FROM INFO
WHERE (age NOT IN(select max(age) from INFO group by type,name))
不知道为什么,删不掉它!
如果把NOT IN改为IN,会把所有数据都删掉
请大家看下问题出在哪里啊?
也就是说如果有多条数据供选择,机器会全删掉的。
WHERE age NOT IN
(select age
from
(select max(age) age,type,name
from INFO group by type,name
) A)
看看
DELETE FROM INFO WHERE NOT EXISTS (SELECT 1 FROM INFO I WHERE I.TYPE=INFO.TYPE AND INFO.NAME=I.NAME AND INFO.AGE<I.AGE)
是的,因为你加入了 group by type,name
我写的SQL语句有错误的,因为select max(age) from INFO group by type,name会把所有的age都取到。
(
type int
,name varchar(13)
, age int
)insert into @t values(1,'a',13)
insert into @t values(1,'b',14)
insert into @t values(1,'b',13)
select max(age) from @t group by type,name-------问题出在这里---结果-----13
14
-------
按楼主的写法当然删除不掉了