表名:JOBHISTORY
字段:EMPNO: 雇员号(INT)NOT NULL
POSITION:职位 (VARCHAR)NOT NULL
STARTDATE:开始日期(DATE)NOT NULL
ENDDATE:结束日期(DATE) NULL
SALARY:工资(DECIMAL)NOT NULL
说明:该表为雇员的工作历史,雇员号和职位为主键,ENDDATE为空表示现在正在进行的工作,不为空表示原来的工作.EMPNO参照EMPLOYEE表的EMPNO,(与此题无关)题目如下:
List the number of months which employee 17 has been in each of his jobs in the company. Use the function ROUND to get the number of months as a whole number.我做了一个错误的如下:
SELECT ROUND(CAST(Current_Date-J.STARTDATE AS INT)/12,0)
AS CNT_MONTH FROM JOBHISTORY J
WHERE J.EMPNO=17 and enddate is null
UNION
SELECT ROUND(CAST(J.ENDDATE-J.STARTDATE AS INT)/12,0)
AS CNT_MONTH FROM JOBHISTORY J
WHERE J.EMPNO=17 and enddate is NOT null
结果:
CNT_MONTH
100
608 错误提示为:
A column needed to answer the question is missing: column 1
A column in your answer is unnecessary: column 1
表示:我的投影都错了.
以上可能是我对题目理解错了.谁能为我写一个语句,谢谢!!!
字段:EMPNO: 雇员号(INT)NOT NULL
POSITION:职位 (VARCHAR)NOT NULL
STARTDATE:开始日期(DATE)NOT NULL
ENDDATE:结束日期(DATE) NULL
SALARY:工资(DECIMAL)NOT NULL
说明:该表为雇员的工作历史,雇员号和职位为主键,ENDDATE为空表示现在正在进行的工作,不为空表示原来的工作.EMPNO参照EMPLOYEE表的EMPNO,(与此题无关)题目如下:
List the number of months which employee 17 has been in each of his jobs in the company. Use the function ROUND to get the number of months as a whole number.我做了一个错误的如下:
SELECT ROUND(CAST(Current_Date-J.STARTDATE AS INT)/12,0)
AS CNT_MONTH FROM JOBHISTORY J
WHERE J.EMPNO=17 and enddate is null
UNION
SELECT ROUND(CAST(J.ENDDATE-J.STARTDATE AS INT)/12,0)
AS CNT_MONTH FROM JOBHISTORY J
WHERE J.EMPNO=17 and enddate is NOT null
结果:
CNT_MONTH
100
608 错误提示为:
A column needed to answer the question is missing: column 1
A column in your answer is unnecessary: column 1
表示:我的投影都错了.
以上可能是我对题目理解错了.谁能为我写一个语句,谢谢!!!
from JOBHISTORY
WHERE EMPNO=17
group by POSITION
from JOBHISTORY
WHERE EMPNO=17
group by POSITION RESULT:
MONTH
7301
1203 A column needed to answer the question is missing: column 1
A column in your answer is unnecessary: column 1
Accuracy Score = 0%
17号在表中的数据如下:
17 ,Analyst Programmer ,17-JAN-88 , null, 21000 --现在的工作
17 ,Programmer, 1-OCT-84 , 17-JAN-88,16000 --原来的工作
select round(months_between(nvl(ENDDATE,sysdate)- STARTDATE)) from dual还有可能题目的意思是:
select months_between(round(nvl(ENDDATE,sysdate),'MONTH')-round(STARTDATE,'MONTH')) from dual英文不行啊,题目意思看不懂啊,,,,
解决如下:
SELECT ROUND(months_between(nvl(ENDDATE,sysdate),STARTDATE)) FROM JOBHISTORY J
WHERE J.EMPNO=17;result:
ROUND(MONTHS_BETWEEN(NVL(ENDDATE,SYSDATE),STARTDATE))
240
40 all needed columns to answer the question have been found in your SQL
no unnecessary columns have been found in your SQL
Accuracy Score = 100% Think you!!!
FROM JOBHISTORY J
WHERE J.EMPNO=17
GROUP BY POSITION
题目是列出17号员工每一个职位所在时间,并使用函数RUOND以整月的形式表示。