在實際的開發當中有時候會有讓你做統計、做報表。
比如在學生選課管理系統中讓你寫出所有的學生的每一門可的成績,這時候我們寫的SQL得出的結果一般是每一個學生選了好多課,其中每一個課和對應的成績都是一行記錄。
如下格式:
NAME COURSE GREAD
---- ------ -----
dai java 60
dai c# 70
dai c 80
dai sql 90
往往上面的格式不是我們想要的,我們想要的格式像如下所示:
NAME JAVA C# C SQL
---- ---- -- -- ---
dai 60 70 80 90
下面我們就構造一個完整的示例來看看上面的情況要如何實作:
CREATE TABLE t(
name VARCHAR2(10),
course VARCHAR2(10),
gread NUMBER
)
BEGIN
INSERT INTO t VALUES('dai', 'java', 60);
INSERT INTO t VALUES('dai', 'c#', 70);
INSERT INTO t VALUES('dai', 'c', 80);
INSERT INTO t VALUES('dai', 'sql', 90);
INSERT INTO t VALUES('tu', 'java', 90);
INSERT INTO t VALUES('tu', 'c#', 80);
INSERT INTO t VALUES('tu', 'c', 70);
INSERT INTO t VALUES('tu', 'sql', 60);
END;
以上的代碼構造好了資料。
下面我們分成兩部來實作學生的成績報表:
1、通過使用DECODE實作行列轉換,查詢出每個學生選的課程和相應的成績。
SELECT name,
DECODE(course, 'java', gread) AS java,
DECODE(course, 'c#', gread) AS c#,
DECODE(course, 'c', gread) AS c,
DECODE(course, 'sql', gread) AS sql
FROM t;
NAME JAVA C# C SQL
---------- ---------- ---------- ---------- ----------
dai 60
dai 70
dai 80
dai 90
tu 90
tu 80
tu 70
tu 60
2、通過使用聚合函數MAX()來實作化散為整
通過第一步的結果,我們發現使用DECODE構造出了由course字段組成了行,但是我們發現每一個課程和相應的成績還是出現一行。但是你仔細觀察,是不是隻要将同一姓名的每一行往上壓縮一下,就變成了一行了。那我們要怎麼做到嘞?在這裡我們應該想到使用分組(GROUP BY)來實作。
SELECT name,
MAX(DECODE(course, 'java', gread)) AS java,
MAX(DECODE(course, 'c#', gread)) AS c#,
MAX(DECODE(course, 'c', gread)) AS c,
MAX(DECODE(course, 'sql', gread)) AS sql
FROM t
GROUP BY name;
NAME JAVA C# C SQL
---------- ---------- ---------- ---------- ----------
dai 60 70 80 90
tu 90 80 70 60
看得到我們想要的結果了吧。
現在不是流行說"那麼,問題就來了!"這句話麼。的确!對于細心的人來說那麼問題就來了。
在這裡我們為什麼要使用MAX()而不使用SUM()、AVG()等等聚合函數?告訴大家其實在示例中确實上面3個聚合函數都能使用并且都能成功實作。但是如果在聚合函數裡面的值是 字元型的使用SUM()、AVG()還行麼?答案是不行了,是以使用MAX()也算是一種相容的考慮的。