1.理論就是先彙總在排序
with countzybh as
(select s.zybh,t.zymc,t.備註,count(1) as scount from
student s inner join specialty t on s.zybh=t.zybh
group by s.zybh,t.zymc,t.備註
)
select zybh,zymc,備註, scount ,
rank() over( order by scount desc) as sort
from countzybh
2.直接left join 加case when 即可實現
select s.a , case when t.c is null then s.b else t.c end as c from s left join t on s.c=t.c
1.理論就是先彙總在排序
with countzybh as
(select s.zybh,t.zymc,t.備註,count(1) as scount from
student s inner join specialty t on s.zybh=t.zybh
group by s.zybh,t.zymc,t.備註
)
select zybh,zymc,備註, scount ,
rank() over( order by scount desc) as sort
from countzybh
2.直接left join 加case when 即可實現
select s.a , case when t.c is null then s.b else t.c end as c from s left join t on s.c=t.c