这是一个SQL SERVER下的查询,请教各位在Oracle下应该如何来写?谢谢!SELECT
A.考试ID,
A.考试科目,
A.考试日期,
ISNULL(CAST(DATEPART(YEAR,A.考试日期) AS VARCHAR) + '年'
+ CAST(DATEPART(month,A.考试日期) AS VARCHAR) + '月'
+ CAST(DATEPART(day,A.考试日期) AS VARCHAR) + '日','') AS 考试日期,
B.监考老师姓名
FROM 考试表 A
LEFT JOIN 监考老师表 B
ON A.考试ID = B.考试ID
AND B.监考老师ID = (SELECT MAX(监考老师ID) FROM 监考老师表 WHERE 考试ID=A.考试ID)
WHERE A.考试状态 = 1
A.考试ID,
A.考试科目,
A.考试日期,
ISNULL(CAST(DATEPART(YEAR,A.考试日期) AS VARCHAR) + '年'
+ CAST(DATEPART(month,A.考试日期) AS VARCHAR) + '月'
+ CAST(DATEPART(day,A.考试日期) AS VARCHAR) + '日','') AS 考试日期,
B.监考老师姓名
FROM 考试表 A
LEFT JOIN 监考老师表 B
ON A.考试ID = B.考试ID
AND B.监考老师ID = (SELECT MAX(监考老师ID) FROM 监考老师表 WHERE 考试ID=A.考试ID)
WHERE A.考试状态 = 1
SELECT
A.考试ID,
A.考试科目,
CASE A.考试科目
WHEN '数学' THEN '理科'
ELSE '文科'
END AS 科目类型,
A.考试日期,
ISNULL(CAST(DATEPART(YEAR,A.考试日期) AS VARCHAR) + '年'
+ CAST(DATEPART(month,A.考试日期) AS VARCHAR) + '月'
+ CAST(DATEPART(day,A.考试日期) AS VARCHAR) + '日','') AS 考试日期,
B.监考老师姓名
FROM 考试表 A
LEFT JOIN 监考老师表 B
ON A.考试ID = B.考试ID
AND B.监考老师ID = (SELECT MAX(监考老师ID) FROM 监考老师表 WHERE 考试ID=A.考试ID)
WHERE A.考试状态 = 1
A.考试ID,
A.考试科目,
CASE A.考试科目
WHEN '数学' THEN '理科'
ELSE '文科'
END AS 科目类型,
A.考试日期,
to_char(A.考试日期, 'yyyy') || '年'
|| to_char(A.考试日期, 'mm') || '月'
|| to_char(A.考试日期, 'dd') || '日' AS 考试日期,
B.监考老师姓名
FROM 考试表 A
LEFT JOIN 监考老师表 B
ON A.考试ID = B.考试ID
AND B.监考老师ID = (SELECT MAX(监考老师ID) FROM 监考老师表 WHERE 考试ID=A.考试ID)
WHERE A.考试状态 = 1
A.考试ID,
A.考试科目,
A.考试日期,
to_char(A.考试日期, 'yyyy') || '年'
|| to_char(A.考试日期, 'mm') || '月'
|| to_char(A.考试日期, 'dd') || '日' AS 考试日期 ,
B.监考老师姓名
FROM 考试表 A
LEFT JOIN 监考老师表 B
ON A.考试ID = B.考试ID
AND B.监考老师ID = (SELECT MAX(监考老师ID) FROM 监考老师表 WHERE 考试ID=A.考试ID)
WHERE A.考试状态 = 1
最近比较忙,所以拖了这么久才来结贴,请见谅啊!
感谢两位的热心帮忙,不过后来我客户那测试时的发现没法使用JOIN语法,查了资料才知道原来客户的Oracel是8.1的,不支持Join语句,不过现在已经解决了,再此仍要感谢两们的热心参与,分数不多只有二十分,就一人一半吧!