表:T1,T2
No为主键和外键
T1表 字段:No,Name
T2表 字段:No,COURSE,SCORE
数据样例:
T1表: No Name
A1 zhangsan
A2 lisi
T2表: No COURSE SCORE
A1 Englist 80
A1 Math 91
A2 English 88
A2 Math 79
A2 Physics90
希望得到如下数据:(前两个字段相同时,只显示第一行,下面为空字符)
No Name COURSE SCORE
A1 zhangsan Englist 80
Math 91
A2 lisi Englist 88
Math 79
Physics 90
No为主键和外键
T1表 字段:No,Name
T2表 字段:No,COURSE,SCORE
数据样例:
T1表: No Name
A1 zhangsan
A2 lisi
T2表: No COURSE SCORE
A1 Englist 80
A1 Math 91
A2 English 88
A2 Math 79
A2 Physics90
希望得到如下数据:(前两个字段相同时,只显示第一行,下面为空字符)
No Name COURSE SCORE
A1 zhangsan Englist 80
Math 91
A2 lisi Englist 88
Math 79
Physics 90
select t1.no,t1.name,t2.course,t2.score,row_number() over(partition by t1.no,t1.name order by t2.course) rn from t1,t2
where t1.no = t2.no)