有一个表 grade,其字段和记录如下:
course score
语文 88
数学 67
英语 55要求查询的结果如下:
语文 数学 英语
优秀 及格 不及格
其中score>=80为优秀 80>score>=60为及格 score<60为不及格请问用sql怎么写,数据库为oracle,谢谢大家了
course score
语文 88
数学 67
英语 55要求查询的结果如下:
语文 数学 英语
优秀 及格 不及格
其中score>=80为优秀 80>score>=60为及格 score<60为不及格请问用sql怎么写,数据库为oracle,谢谢大家了
SELECT '语文' course,88 score from dual
union
SELECT '数学' course,67 score from dual
union
SELECT '英语' course,55 score from dual
)SELECT (case when 语文>=80 then '优秀' when 语文<80 and 语文>=60 then '及格' else '不及格' end) 语文,
(case when 数学>=80 then '优秀' when 数学<80 and 数学>=60 then '及格' else '不及格' end) 数学,
(case when 英语>=80 then '优秀' when 英语<80 and 英语>=60 then '及格' else '不及格' end) 英语
from (select
MAX(decode(course, '语文', score, NULL)) AS 语文,
MAX(decode(course, '数学', score, NULL)) AS 数学,
MAX(decode(course, '英语', score, NULL)) AS 英语
FROM t);
借用楼上大哥的数据啦。
SELECT (case
when Chinese >= 80 then
'优秀'
when Chinese < 80 and Chinese >= 60 then
'及格'
else
'不及格'
end) AS Chinese,
(case
when Math >= 80 then
'优秀'
when Math < 80 and Math >= 60 then
'及格'
else
'不及格'
end) AS Math,
(case
when English >= 80 then
'优秀'
when English < 80 and English >= 60 then
'及格'
else
'不及格'
end) AS English
from (select *
from t pivot(max(score) for course in ('语文' as Chinese, '数学' AS Math, '英语' AS English)))
所以这个SQL应该这样写:
WITH
t_fen AS
(SELECT 'yuwen' course, 88 score FROM dual UNION
SELECT 'shuxue' course, 67 score FROM dual UNION
SELECT 'yingyu' course, 55 score FROM dual),
m_fen AS
(SELECT 80 minfen, 101 maxfen, 'Good' result FROM dual UNION
SELECT 60 minfen, 80 maxfen, 'Pass' result FROM dual UNION
SELECT 0 minfen, 60 maxfen, 'Failed' result FROM dual)SELECT t.course, m.result
FROM t_fen t left join m_fen m ON t.score >= m.minfen AND t.score <= m.maxfen
course result
1 yingyu Failed
2 shuxue Pass
3 yuwen Good
SELECT max(DECODE(M.COURSE, '语文', M.RANK_DESC)) 语文,
max(DECODE(M.COURSE, '数学', M.RANK_DESC)) 数学,
max(DECODE(M.COURSE, '英语', M.RANK_DESC)) 英语
FROM (SELECT T.COURSE,
T.SCORE,
CASE
WHEN T.SCORE >= 80 THEN
'优秀'
WHEN T.SCORE >= 60 AND T.SCORE < 80 THEN
'及格'
ELSE
'不及格'
END RANK_DESC
FROM GRADE T) M