创建表的数据
name codeid batchno
a 1 A
a 1 B
a 1 C
a 2 A
a 2 B
a 2 C
a 3 A
a 3 B
a 3 C
.....
b 1 A
b 1 B
b 1 C
b 2 A
b 2 B
b 2 C
b 3 A
b 3 B
b 3 C
......
要得到最后的结果为:
a 1 A
a 2 B
a 3 C
.......
b 1 A
b 2 B
b 3 C
........
问题来源:http://www.iteye.com/problems/51078
我的解决过程:
CREATE TABLE TEST(tname VARCHAR(2),codeid NUMBER(20),bacthno VARCHAR(2));
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','1','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','1','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','1','C');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','2','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','2','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','2','C');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','3','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','3','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('a','3','C');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','1','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','1','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','1','C');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','2','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','2','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','2','C');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','3','A');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','3','B');
INSERT INTO TEST (tname,codeid,bacthno) VALUES ('b','3','C');
SELECT * FROM TEST;
得到:
name codeid batchno
a 1 A
a 1 B
a 1 C
a 2 A
a 2 B
a 2 C
a 3 A
a 3 B
a 3 C
b 1 A
b 1 B
b 1 C
b 2 A
b 2 B
b 2 C
b 3 A
b 3 B
b 3 C
SELECT * FROM TEST T WHERE ASCII(T.BACTHNO)-T.CODEID=64 ORDER BY TNAME;
得到:
a 1 A
a 2 B
a 3 C
b 1 A
b 2 B
b 3 C