二個數據表,一個員工資料表(Users),另外一個員工學歷表(UserInfo);執行sql語結果為:
sql Code
select
a.userno,
(select username from users where userno=a.userno)as username,
(select address from users where userno=a.userno)as address,
a.本科,
a.專科,
a.高中,
a.初中,
a.小學
from (
select
b.userno,
max(case b.subject when '本科' then b.school1 else null end) as 本科,
max(case b.subject when '專科' then b.school1 else null end) 專科,
max(case b.subject when '高中' then b.school1 else null end) 高中,
max(case b.subject when '初中' then b.school1 else null end) 初中,
max(case b.subject when '小學' then b.school1 else null end) 小學
from UserInfo b group by b.UserNo
)a