表结构及数据准备:
-- ----------------------------
-- Table structure for STUDENT_SCORE
-- ----------------------------
DROP TABLE "STUDENT_SCORE";
CREATE TABLE "STUDENT_SCORE" (
"NAME" VARCHAR2(20 BYTE) VISIBLE,
"SUBJECT" VARCHAR2(20 BYTE) VISIBLE,
"SCORE" NUMBER(4,1) VISIBLE
)
;
-- ----------------------------
-- Records of STUDENT_SCORE
-- ----------------------------
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '语文', '11');
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '数学', '22');
INSERT INTO "STUDENT_SCORE" VALUES ('张三', '英语', '33');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '语文', '44');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '数学', '55');
INSERT INTO "STUDENT_SCORE" VALUES ('李四', '英语', '66');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '语文', '77');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '数学', '88');
INSERT INTO "STUDENT_SCORE" VALUES ('王五', '英语', '99');
decode行转列:
SELECT
name "姓名",
sum( decode( subject, '语文', nvl( score, 0 ), 0 ) ) "语文",
sum( decode( subject, '数学', nvl( score, 0 ), 0 ) ) "数学",
sum( decode( subject, '英语', nvl( score, 0 ), 0 ) ) "英语"
FROM
student_score
GROUP BY
name;
-- group之后可以显示group by的那些列或者通过函数显示
--decode(条件,值1,返回值1,值2,返回值2.....值n,返回值n,default)
if 条件=值1 then
return 返回值1;
elsif 条件=值2 then
return 返回值2;
.....
elsif 条件=值n then
renturn 返回值3;
else
return default;
end if
--NVL(eExpression1, eExpression2)
if eExpression1==NULL
then
return eExpression1;
else
return eExpression2;
endif
case when 行转列
SELECT
name "姓名",
sum( CASE WHEN subject = '语文' THEN nvl( score, 0 ) ELSE 0 END ) "语文",
sum( CASE WHEN subject = '数学' THEN nvl( score, 0 ) ELSE 0 END ) "数学",
sum( CASE WHEN subject = '英语' THEN nvl( score, 0 ) ELSE 0 END ) "英语"
FROM
student_score
GROUP BY
name;
数据库图:
实现效果图: