有点麻烦,不过还是能够实现,首先找出每个featureid对应的最大detailid,然后再根据detailid 找到所谓的第一个userid,然后featureid,userid的分组中选择最小的detailidselect e.featureid,d.userid, min(e.detailid) from request d, detail e,
( select b.featureid, a.userid from request a, detail b,
(select featureid, Max(detailid) max_detailid from detail group by featureid ) c
where a.requestid = b.requestid and b.featureid=c.featureid and b.detailid = c.max_detailid ) f
where d.userid = f.userid and d.requestid=e.requestid and e.featureid = f.featureid
group by e.featureid,d.userid
( select b.featureid, a.userid from request a, detail b,
(select featureid, Max(detailid) max_detailid from detail group by featureid ) c
where a.requestid = b.requestid and b.featureid=c.featureid and b.detailid = c.max_detailid ) f
where d.userid = f.userid and d.requestid=e.requestid and e.featureid = f.featureid
group by e.featureid,d.userid
解决方案 »
- !=和<>的区别
- oracle 数据记录过滤 最佳性能sql
- 还是ORA-04030错误,高手请指教,如何解决
- 如何修改表中某列相同的部分?
- 没用过Oracle求帮助,将sql改为Oracle
- 多行变多列问题,(并非多行变一列,稍微有点不同)求高手,急。
- 请问imp/exp如何实现将数据导入到另外一张表中?两张表结构相同
- 请教oracle9i中建立测试用户和测试库的问题
- 请教高手一个SQL语句
- 安装oracle8.17时,配置工具中Net8 configuration Assistant这一项过不去了,请各位大侠指教!
- 救急啊,怎样把以下几个mssql存储过程转到oracle下,剩下的两个
- oracle8.1.6用什么建模工具好?
非常感谢你的回复,但好像结果还是不对的
你选出来的最小detailId是整个FeatureId分组中最小的了!
我的表达水平实在是对不起大家其实题目的意思是很简单的,但选择真是不好选,实在不行就拥游标了,但还是不甘心
我估计Lastdrop(空杯) 是看懂我意思了,可是结果好像是错的group by e.featureid,d.userid 和Group by e.FeatueId 在这里好像是没什么区别吧我想了一下 可能要用个having(...) ,但头大了
想不动了
select yyy.featureid,xxx.userid,max(yyy.detailid)
from request xxx , detail yyy, (select b.featureid,b.detailid,row_number() over(partition by b.featureid order by b.detailid desc) as num,a.userid
from request a , detail b
where a.requestid=b.requestid) zzz
where xxx.requestid=yyy.requestid
and xxx.userid = zzz.userid
and zzz.num=1
and zzz.detailid=yyy.detailid
group by yyy.featureid,xxx.userid
select yyy.featureid,xxx.userid,max(yyy.detailid)
from request xxx , detail yyy, (select b.featureid,b.detailid,row_number() over(partition by b.featureid order by b.detailid desc) as num,a.userid
from request a , detail b
where a.requestid=b.requestid) zzz
where xxx.requestid=yyy.requestid
and xxx.userid = zzz.userid
and zzz.num=1
group by yyy.featureid,xxx.userid
select e.featureid,d.userid, min(e.detailid) from request d, detail e,
( select b.featureid, a.userid from request a, detail b,
(select featureid, Max(detailid) max_detailid from detail group by featureid ) c
where a.requestid = b.requestid and b.featureid=c.featureid and b.detailid = c.max_detailid ) f
where d.userid = f.userid and d.requestid=e.requestid and e.featureid = f.featureid
and e.detailid > ( select max(detailid) from detail g where g.featureid = f.featureid
and g.requestid <> d.requestid )
group by e.featureid,d.userid
2 detail a,request b,
3 (select featureid,userid from detail a, request b where a.requestid=b.requestid
4 and detailid in (select max(detailid) from detail group by featureid)) c
5 where a.requestid=b.requestid and b.userid=c.userid
6 and a.featureid=c.featureid
7 and detailid>(select max(detailid) from detail c,request d where
8 featureid=a.featureid and c.requestid=d.requestid and d.userid<>b.userid
9 group by featureid)
10 group by a.featureid,b.userid; FEATUREID USERID MIN(DETAILID)
---------- ---------- -------------
1 1 99
2 1 200
3 5 300搞定,呵呵
SQL> select a.featureid,b.userid,min(detailid) from
2 detail a,request b,
3 (select featureid,userid from detail a, request b where a.requestid=b.requestid
4 and detailid in (select max(detailid) from detail group by featureid)) c
5 where a.requestid=b.requestid and b.userid=c.userid
6 and a.featureid=c.featureid
7 and detailid>(select max(detailid) from detail c,request d where
8 featureid=a.featureid and c.requestid=d.requestid and d.userid<>b.userid)
9 group by a.featureid,b.userid; FEATUREID USERID MIN(DETAILID)
---------- ---------- -------------
1 1 99
2 1 200
3 5 300
( select b.featureid, a.userid from request a, detail b,
(select featureid, Max(detailid) max_detailid from detail group by featureid ) c
where a.requestid = b.requestid and b.featureid=c.featureid and b.detailid = c.max_detailid ) f
where d.userid = f.userid and d.requestid=e.requestid and e.featureid = f.featureid
and e.detailid > ( select max(detailid) from detail g where g.featureid = f.featureid
and g.requestid <> d.requestid )
group by e.featureid,d.userid