SQL語法一問 - Ans.fyi 參考答案

SQL語法一問

2008-10-14 10:39 pm

我有個table分別有username,userid,supervisorid,hiredate,sal...當中hiredate係date format(即係dd/mmm/yyyy),想問下以下兩條問題用sql係點寫呀...???

Q1: Who were hire on the First of May, the Labour Day? Show their names & hiredates. (我唔知點可以只按月份同日期去找..??)

Q2: Show the employeeid, name, and their supervisorid & name.
(即係要做到下面咁既情況...)

Employee ID Supervisor Name ID
-------------------------------------------------
Peter 452 Ken 451
May 453 Peter 452
John 454 Ken 451
Candy 455 Peter 452
Sandy 456 May 453

回答 (1)

garlic2010

2008-10-15 12:32 pm

✔ 最佳答案

Q1. 視乎所用的數據庫

Access, SQL Server
select username,hiredate from employee where MONTH(hiredate)=5 and DAY(hiredate)=1

mysql
select username,hiredate from employee where MONTH(hiredate)=5 and DAYOFMONTH(hiredate)=1

有些數據庫 (例如 Oracle) 並無 MONTH，DAY之類的 function，不過同其他 function 一齊用都可以找到日期欄位中的月和日。

Q2. 這題須要用 self-join 去找 supervisor name

select e.userid, e.username, e.supervisorid, s.username as supervisorname
from employee e, employee s where e.supervisorid = s.userid

Ansi standard sql
select e.userid, e.username, e.supervisorid, s.username as supervisorname
from employee e inner join employee s
on e.supervisorid = s.userid

👍 0 👎 0