select min(id),min(name),other fields....
from table
group by id,name
from table
group by id,name
解决方案 »
- 数据库删除数据出现键列信息不足或不正确解决办法
- 请问,如何用sql2005把本地数据库导入到远程服务器中?
- 123
- [update][char]字段中,不可见字符的去除问题
- 如何 访问 sql server 2008 filestream 文件组中 的文件
- 看看我的存储过程有什么错误.
- 如何综合查询?
- mssql日志恢复问题
- 难点问题!
- 怎样用编程实现创建SQL Sever数据库的内容?比如Click一下,就在Sql Server里面创建了数据库(如:名字Amass100HR0),最好编一段代码。用C++Build更好。
- 请教vf问题:数值型转换成日期型的函数是哪个?如不能直接转换,应该怎样处理?很急!谢谢!
- Adodc.recordsource赋值问题,请指点。
select min(id),min(name),other fields....
from table
group by id,name 然后delete temp_table最后将需要的数据倒回来就可以了
並且說出你的DBMS
如果其余项相同,就淘汰掉 name="system"的项 啊!!!!!!!!
2、是不是重复情况下,一定会有一个记录的NAME是SYSTEM?
id | name | Path | show |1 c:\win.exe c:\1234.exe 03 c:\hello.exe c:\test.exe 14 d:\super.exe c:\w32.exe 07 system d:\rr.exe 1谢谢
7 system d:\rr.exe 1
select *
from log ,
(select path,show from log where name="system") t1
where log.path=t1.path and log.show=t1.show and log.name<>"system"
select log.id,log.name,log.path,log.show
from log ,
(select path,show from log where name="system") t1
where log.path=t1.path and log.show=t1.show and log.name<>"system"
select a.* from log a where exists(select b.id1 from
(select min(id) id1 from log group by path) b where a.id=b.id)
select a.* from log a where exists(select b.id1 from
(select min(id) id1 from log group by path) b where a.id=b.id1)
先添加一个标识字段 flag
alter table table_name add flag int not null default(0)--先将只有一条的记录标识起来。
update table_name set flag = 1 from
( select min(ID) as minID , count(ID) as num from table_name group by other fields ) tt
where tt.num = 1 and ID = tt.minID--然后删除有两条重复信息中name= 'system'的记录delete table_name where flag = 0 and name = 'system'最后删除flag字段
步骤太多啦些,不直接
from table1 as x
where path + "-" + show in (select path + "-" + show from table1
group by path, show
having count(*) > 1)
and name = "system";
(select min(id) id1 from log group by path) b 表示取path相同的最小的id的集合,因为如果path如有重复,取小的id符合条件“如果有一个 xxx.exe 就一定会有一个 system 与其相对应,这时侯 system 的id 一定会比 xxx.exe 大”,如果path没重复,max(id)就是直接取唯一的id,而你需要的纪录集的每一个id都在我的b表里,你不需要的id都不再我的b表里,这样就很容易取的log表id在b表里就取出来,这就是最终答案了。没有测试,不知道结果如何,思路反正就是这样。
select a.* from log a where a.id in(select b.id1 from
(select min(id) id1 from log group by path) b);
那么这就是我们要保留的记录。
delete from table_name
where ( table_name.name = 'system'
and
not table_name.ID in (select min(ID) as minID as num from table_name group by other fields )
)
delete
from table1 as x
where path + "-" + show in (select path + "-" + show from table1
group by path, show
having count(*) > 1)
and id not in (select min(id) from table1 as y
where y.path = x.path
and y.show = x.show
and y.name <> "system");
where ( table_name.name = 'system'
and
not table_name.ID in (select min(ID) from table_name group by other fields )
)
delete from table_name
where table_name.ID not in (select min(ID) from table_name group by other fields )