17,377
社区成员
发帖
与我相关
我的任务
分享
SELECT ID,NAME FROM ( SELECT t.*,RANK() OVER (PARTITION BY ID ORDER BY NAME) rn FROM table1 t) a WHERE a.rn=1;
SELECT tab1.ID,tab1.NAME
from USER tab1 inner join (select ID,max(NAME) as NAME from USER group by ID)tab2 on tab1.ID=tab2.ID
where
tab1.NAME=tab2.NAME
SELECT tab1.*,tab2.name FROM tabl,
(SELECT ID,NAME FROM ( SELECT t.*,RANK() OVER (PARTITION BY ID ORDER BY NAME) rn FROM tab2 t) a WHERE a.rn=1) tab2
WHERE tab1.id=tab2.id;
---sql有点复杂..呵呵..抛砖引玉吧...好象hql还不行
SQL> with tab as
2 (
3 select 1 id, 'aa' name
4 from dual
5 union all
6 select 2 id, 'bb' name
7 from dual
8 union all
9 select 1 id, 'dd' name
10 from dual
11 union all
12 select 2 id, 'ee' name from dual
13 )
14 select t.id, t.name
15 from tab t,
16 (select id, name
17 from (select id,
18 name,
19 row_number() over(partition by id order by dbms_random.value()) rn
20 from tab) tt
21 where tt.rn = 1) t1
22 where t1.id = t.id
23 and t1.name = t.name
24 /
ID NAME
---------- ----
1 dd
2 bb
SQL>
SQL> with tab as
2 (
3 select 1 id, 'aa' name
4 from dual
5 union all
6 select 2 id, 'bb' name
7 from dual
8 union all
9 select 1 id, 'dd' name
10 from dual
11 union all
12 select 2 id, 'ee' name from dual
13 )
14 select t.id, t.name
15 from tab t,
16 (select id, name
17 from (select id,
18 name,
19 row_number() over(partition by id order by dbms_random.value()) rn
20 from tab) tt
21 where tt.rn = 1) t1
22 where t1.id = t.id
23 and t1.name = t.name
24 /
ID NAME
---------- ----
1 aa
2 ee
SQL>