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

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
...全文
122 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
希望有些启发!

28,409

社区成员

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧