我的表中有这些记录:
id sequence question answer letter
1 1 1 + 1 2 B
2 2 2 + 2 4 E
3 3 4 + 4 8 A
4 4 8 + 8 16 D
5 5 16 + 16 32 C
有什么可能使我的桌子看起来像这样吗?:
id sequence question answer letter
1 1 1 + 1 8 A
2 2 2 + 2 2 B
3 3 4 + 4 32 C
4 4 8 + 8 16 D
5 5 16 + 16 4 E
我的意思是第一个是我按顺序排列,但我也想按字母顺序排列,因为如果没有,它们将处于同一位置,因此问题可以轻松回答,我希望答案随字母一起出现并按字母顺序排列。请急需您的帮助:(
-DDL
CREATE TABLE test(id int,sequence int,question varchar(20),answer int, letter varchar(5) );
INSERT INTO test VALUES(1,1,'1+1',2,'B');
INSERT INTO test VALUES(2,2,'2+2',4,'E');
INSERT INTO test VALUES(3,3,'4+4',8,'A');
INSERT INTO test VALUES(4,4,'8+8',16,'D');
INSERT INTO test VALUES(5,5,'16+16',32,'C');
--SQL
SELECT A.id,
A.sequence,
A.question,
B.answer,
B.letter
FROM
(
SELECT id,
sequence,
question,
ROW_NUMBER() OVER ( ORDER BY id ) AS rn
FROM test
) A
INNER JOIN
(
SELECT answer,
letter,
ROW_NUMBER() OVER ( ORDER BY letter ) AS rn
FROM test
) B
ON A.rn = B.rn;
SQL小提琴: - http://sqlfiddle.com/#!6/96e09/3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句