筛选重复字段的记录,求助?急,急,急!!!

kevin_hekai 2004-12-06 02:04:44
id username fileclass uploadtime
1 yy 6 2004-11-30 11:27:08.000
2 rr 6 2004-11-30 11:24:08.000
3 ff 6 2004-11-30 11:07:31.000
4 ff 5 2004-11-30 10:58:19.000
5 gg 5 2004-11-30 10:17:59.000
6 hh 4 2004-11-30 10:16:37.000
7 ee 6 2004-11-29 16:16:38.000
8 ww 6 2004-11-29 16:10:38.000
9 gg 3 2004-11-27 14:56:28.000
10 uu 3 2004-11-27 13:49:38.000
...

现在要筛选出username字段不重复而且fileclass=6的纪录,我写了一个但不成功,在线求助!!

SELECT top 10* FROM files_log_sql WHERE username IN (SELECT distinct username FROM files_log_sql where fileclass=6)order by uploadtime DESC
...全文
89 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lienzhu 2004-12-06
分析你的sql要求

select top 10 * from files_log_sql where fileclass=6 and username in (select username from files_log_sql group by username having count(*)=1) order by uploadtime DESC
回复
fenlin 2004-12-06
SELECT top 10 * FROM files_log_sql WHERE username IN(SELECT distinct username FROM files_log_sql) and fileclass=6 order by uploadtime DESC
回复
lienzhu 2004-12-06
select * from files_log_sql where fileclass=6 and username in (select username from files_log_sql group by username having count(*)=1)
回复
luobinsen007 2004-12-06
SELECT DISTINCT username FROM files_log_sql
where fileclass = 6
回复
rustical 2004-12-06
第一种办法:保留 大的 id
select * from files_log_sql where id in
( select max(id) as id from files_log_sql where fileclass='6' group by username)

第二种办法:保留 小的 id
select * from files_log_sql where id in
( select min(id) as id from files_log_sql where fileclass='6' group by username)
回复
rustical 2004-12-06
第一种办法:保留 大的 id
select * from files_log_sql where id in
( select max(id) as id from files_log_sql where fileclass='6' group by username)
and fileclass ='6'

第二种办法:保留 小的 id
select * from files_log_sql where id in
( select min(id) as id from files_log_sql where fileclass='6' group by username)
and fileclass ='6'
回复
esterlee 2004-12-06
select * from files_log_sql where fileclass=6 group by username
回复
hbzh1014 2004-12-06
不知道你有没有什么特别的要求,或许可以解决问题。
select a.Id,a.userName,a.fileclass,b.uploadtime from (select min(id) id,username,fileclass from files_log_sql where fileclass = '6' group by username,fileclass) a
left outer join files_log_sql b on a.id = b.id
希望有些启发!
回复
相关推荐
发帖
ASP
创建于2007-09-28

2.8w+

社区成员

ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
申请成为版主
帖子事件
创建了帖子
2004-12-06 02:04
社区公告
暂无公告