求助一SQL，有点难度，高手进来，在线等！！！！！！！！！！！

Question

A:
user_id  user_name grade
1 a 50
2 b 70
3 c 80
4 d 75
5 e 80B:
group_id user_id
1 1
1 2
1 3
2 4
2 5我现在要找grade大于60，并且每组只出一个成员结果：
2 b 70
5 e 80
或
3 c 80
5 e 80
或
2 b 70
4 d 75
都可以
create table a
(user_id int,user_name varchar(100),grade int);create table b
(group_id int,user_id int);insert into a values(1,'a',50);
insert into a values(1,'b',70);
insert into a values(1,'c',80);
insert into a values(1,'d',75);
insert into a values(1,'e',80);insert into b values(1,1);
insert into b values(1,2);
insert into b values(1,3);
insert into b values(2,4);
insert into b values(2,5);

Answer 1

select user_id,  user_name, grade from 
（select row_number() over(partition by  user_name  order by user_id) rn,a.* from a where
grade>60)
where rn=1
貌似你的B表没用？

Answer 2

   USER_ID USER_NAME                                                                                                 GRADE
---------- ---------------------------------------------------------------------------------------------------- ----------
         1 b                                                                                                            70
         1 c                                                                                                            80
         1 d                                                                                                            75
         1 e                                                                                                            80出的结果不对吧有满足条件的，一个组只出一个用户

Answer 3

select user_id,user_name,grade from(
select a.user_id,a.user_name,a.grade,
row_number()over(partition by b.group_id order by a.user_id) as rn
 from a,b where a.user_id=b.user_id and a.grade>60)a
where rn=1

Answer 4

OPER@tl> select user_id,user_name,grade
  2  from
  3  (select a.*,row_number() over(partition by b.group_id order by b.user_id) rn
  4  from a,b
  5  where a.grade>60
  6  and a.user_id=b.user_id)
  7  where rn=1;   USER_ID USER_NAME                 GRADE
---------- -------------------- ----------
         2 b                            70
         4 d                            75OPER@tl> 

Answer 5

--建立测试环境  
create table a(user_id varchar(20),user_name varchar(20),grade varchar(20)); 
insert into a select '1','a','50' from dual;
insert into a select '2','b','70' from dual;
insert into a select '3','c','80' from dual;
insert into a select '4','d','75' from dual;
insert into a select '5','e','80' from dual;
create table b(group_id varchar(20),user_id varchar(20));
insert into b select '1','1' from dual;
insert into b select '1','2' from dual;
insert into b select '1','3' from dual;
insert into b select '2','4' from dual;
insert into b select '2','5' from dual;
--测试
select user_id,user_name,grade from(
select a.user_id,a.user_name,a.grade,
row_number()over(partition by b.group_id order by a.user_id) as rn
 from a,b where a.user_id=b.user_id and a.grade>60)a
where rn=1;
--删除测试环境
drop table a;
drop table b;/*测试结果
USER_ID USER_NAME GRADE
2 b 70
4 d 75
*/

Answer 6

   USER_ID USER_NAME                                                                                                 GRADE
---------- ---------------------------------------------------------------------------------------------------- ----------
         1 e                                                                                                            80怎么只有一个结果了呢
有两个组，应每个组有满足条件的，出一个用户 

Answer 7

SQL> select user_id, user_name, grade
  2    from (select user_id,
  3                 user_name,
  4                 grade,
  5                 row_number() over(partition by group_id order by dbms_random.value) rn
  6            from (select a.user_id, a.user_name, a.grade, b.group_id
  7                    from a, b
  8                   where a.user_id = b.user_id
  9                     and a.grade > 60))
 10   where rn = 1;
 
                                USER_ID USER_NAME                                                                                                          GRADE
--------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------
                                      2 b                                                                                                                     70
                                      5 e                                                                                                                     80
 
SQL> 
SQL> select user_id, user_name, grade
  2    from (select user_id,
  3                 user_name,
  4                 grade,
  5                 row_number() over(partition by group_id order by dbms_random.value) rn
  6            from (select a.user_id, a.user_name, a.grade, b.group_id
  7                    from a, b
  8                   where a.user_id = b.user_id
  9                     and a.grade > 60))
 10   where rn = 1;
 
                                USER_ID USER_NAME                                                                                                          GRADE
--------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------
                                      2 b                                                                                                                     70
                                      4 d                                                                                                                     75
 
SQL> 
SQL> select user_id, user_name, grade
  2    from (select user_id,
  3                 user_name,
  4                 grade,
  5                 row_number() over(partition by group_id order by dbms_random.value) rn
  6            from (select a.user_id, a.user_name, a.grade, b.group_id
  7                    from a, b
  8                   where a.user_id = b.user_id
  9                     and a.grade > 60))
 10   where rn = 1;
 
                                USER_ID USER_NAME                                                                                                          GRADE
--------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------
                                      3 c                                                                                                                     80
                                      4 d                                                                                                                     75
 

Answer 8

贴一个
select A.user_id , A.user_name , A.grade
from A 
WHERE A.grade > 60 AND A.user_id in ( 
select max(b.user_id)
from b group by group_id )

Answer 9

贴一个
select A.user_id , A.user_name , A.grade
from A 
WHERE A.grade > 60 AND A.user_id in ( 
select max(b.user_id)
from b group by group_id )