select b.s_name as 語文成績排名 from score a join student b on a.s_id=b.s_id where a.c_id='01' order by a.s_score desc;
select b.s_name as 數學成績排名 from score a join student b on a.s_id=b.s_id where a.c_id='02' order by a.s_score desc;
select b.s_name as 英語成績排名 from score a join student b on a.s_id=b.s_id where a.c_id='03' order by a.s_score desc;
每個單獨的sql陳述句都能查出各科的成績排名，但如何合并為一張表呢？像這樣的表
uj5u.com熱心網友回復：
select coalesce(A.rank_1,B.rank_2,C.rank_3) as 名次,語文排名,數學排名,英語排名
from
(select dank() over (order by a.s_scord desc) as rank_1,b.s_name as 語文排名
from score a join student b on a.s_id=b.s_id where a.c_id='01') as A
full join
(select dank() over (order by a.s_scord desc) as rank_2,b.s_name as 數學排名
from score a join student b on a.s_id=b.s_id where a.c_id='02') as B on A.rank_1=B.rank_2
full
(select dank() over (order by a.s_scord desc) as rank_3,b.s_name as 英語排名
from score a join student b on a.s_id=b.s_id where a.c_id='03') as C on isnull(A.rank_1,B.rank_2)=C.rank_3
uj5u.com熱心網友回復：
SQL陳述句關聯很好寫的啊uj5u.com熱心網友回復：
舉個簡單的 cross join 例子，一個表有兩列，一個是 id（或排名吧），一個是內容（比如名字 name）select * from tbl t1, tbl t2 where t1.id = t2.id 就在行上面拼接了 tbl 兩次。
同理，對于你查詢出的每個子表，添加排名列，可以用 rank 函式，然后
select 你的列名 from tbl1, tbl2, tbl3 where tbl1.id = tbl2.id and tbl1.id = tbl3.id 即可。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74745.html
標籤：基礎類
下一篇：這個AD833模塊怎么用？原理圖