oracle数据库中的SQL查询

[zjsxcf]

举例如下表：

name course
A 英语
B 数学
A 法语
C 语文
D 英语

要求查找出A,B,C,D的一个课程，A有两个课程，只需要找出表中的第一个课程（英语）。结果如下，应该如何写SQL?

name course
A 英语
B 数学
C 语文
D 英语

[shiyiwan]

select name, course from t t2 group by name
where not exists (select 1 from t t1 where t1.name = t2.name and t1.rowid < t2.rowid);

[shiyiwan]

select name, course from t t2
where not exists (select 1 from t t1 where t1.name = t2.name and t1.rowid < t2.rowid);

多了个group by ,去掉之

[Adebayor]

SQL> with tt as
2 (select 'A' name, '英语' course
3 from dual
4 union all
5 select 'B', '数学'
6 from dual
7 union all
8 select 'A', '法语'
9 from dual
10 union all
11 select 'C', '语文'
12 from dual
13 union all
14 select 'D', '英语' from dual)
15 select name, course
16 from (select tt.*, row_number() over(partition by name order by rownum) rn
17 from tt
18 where name in ('A', 'B', 'C', 'D'))
19 where rn = 1;

NAME COURSE
---- ------
A 英语
B 数学
C 语文
D 英语

[47522341]

select name,course
from
(
select name,course,
row_number() over(partition by name order by course) r1
from tablename
) t
where r1 = 1

[yurui05]

[TEST1@orcl#23-3月 -10] SQL>select name,course from t6 where exists (select min(rownum) from t6 t where t.rowid>t6.rowid group by name);

NAME COURSE
-------------------- --------------------
A 英语
B 数学
A 法语
C 语文

[jiejiep]

select  name, max(course) from Test group by name;

[wangwfksyc]

select distinct t.name,t.course from t1 t

[中国风]

select *
from (select row=row_number()over(partition by Name order by course),name course from table1 )t
where row=1

[Phoenix_99]

select *
from (select row=row_number()over(partition by Name order by course),name course from table1 )t 
where row=1