SQL进阶练习题

建表语句：

CREATE TABLE Greatests
(keyData CHAR(1) PRIMARY KEY,
 x   INTEGER NOT NULL,
 y   INTEGER NOT NULL,
 z   INTEGER NOT NULL);

插入数据：

INSERT INTO Greatests VALUES('A', 1, 2, 3);
INSERT INTO Greatests VALUES('B', 5, 5, 2);
INSERT INTO Greatests VALUES('C', 4, 7, 1);
INSERT INTO Greatests VALUES('D', 3, 3, 8);

求x和y二者中较大的值

1.使用GREATEST()方法

SELECT keyData,GREATEST(x,y) max FROM Greatests;

2.将x,y合并为一列，然后使用max()方法获取最大值

SELECT keyData,max(col) from 
(
SELECT keyData,x as col from Greatests
UNION all 
SELECT keyData,y as col from Greatests
) tmax GROUP BY keyData ORDER BY keyData;

3.使用case..