SQL服务器:基于
问题描述:
输入和所希望的输出如下所示SQL服务器:基于
输入ID列进行排序的值
id Msg res time
--------------------
1 a1 8 11:14:00
1 qq 8 11:15:00
1 d1 8 11:16:00
1 e1 8 11:17:00
1 f1 8 11:18:00
1 h 4 11:19:00
1 i 4 11:20:00
1 m35 4 11:21:00
1 n 4 11:22:00
1 o 4 11:23:00
1 p0 4 11:23:00
2 a1 4 11:24:00
2 p0 4 11:25:00
2 qq 4 11:26:00
2 c 4 11:27:00
2 h 4 11:28:00
2 o 4 11:29:00
3 c 4 11:30:00
3 qq 4 11:31:00
3 e1 4 11:32:00
希望的输出:
id Msg res time
---------------------
1 a1 8 11:14:00
1 d1 8 11:16:00
1 e1 8 11:17:00
1 f1 8 11:18:00
1 h 4 11:19:00
1 i 4 11:20:00
1 p0 4 11:24:00
1 qq 8 11:15:00
1 m35 4 11:21:00
1 n 4 11:23:00
1 o 4 11:22:00
2 a1 4 11:24:00
2 c 4 11:27:00
2 h 4 11:28:00
2 p0 4 11:25:00
2 qq 4 11:26:00
2 o 4 11:29:00
3 c 4 11:30:00
3 e1 4 11:32:00
3 qq 4 11:31:00
的代码是下面
CREATE TABLE k (id int, Msg varchar(1000), result int, time time);
INSERT INTO k VALUES (‘1’, ‘a1’, ‘8’, ‘11:14:00’)
INSERT INTO k VALUES (‘1’, ‘qq’, ‘8’, ‘11:15:00’)
INSERT INTO k VALUES (‘1’, ‘d1’, ‘8’, ‘11:16:00’)
INSERT INTO k VALUES (‘1’, ‘e1’, ‘8’, ‘11:17:00’)
INSERT INTO k VALUES (‘1’, ‘f1’, ‘8’, ‘11:18:00’)
INSERT INTO k VALUES (‘1’, ‘h’, ‘4’, ‘11:19:00’)
INSERT INTO k VALUES (‘1’, ‘i’, ‘4’, ‘11:20:00’)
INSERT INTO k VALUES (‘1’, ‘m35’, ‘4’, ‘11:21:00’)
INSERT INTO k VALUES (‘1’, ‘n’, ‘4’, ‘11:22:00’)
INSERT INTO k VALUES (‘1’, ‘o’, ‘4’, ‘11:23:00’)
INSERT INTO k VALUES (‘1’, ‘p0’, ‘4’, ‘11:23:00’)
INSERT INTO k VALUES (‘2’, ‘a1’, ‘4’, ‘11:24:00’)
INSERT INTO k VALUES (‘2’, ‘p0’, ‘4’, ‘11:25:00’)
INSERT INTO k VALUES (‘2’, ‘qq’, ‘4’, ‘11:26:00’)
INSERT INTO k VALUES (‘2’, ‘c’, ‘4’, ‘11:27:00’)
INSERT INTO k VALUES (‘2’, ‘h’, ‘4’, ‘11:28:00’)
INSERT INTO k VALUES (‘2’, ‘o’, ‘4’, ‘11:29:00’)
INSERT INTO k VALUES (‘3’, ‘c’, ‘4’, ‘11:30:00’)
INSERT INTO k VALUES (‘3’, ‘qq’, ‘4’, ‘11:31:00’)
INSERT INTO k VALUES (‘3’, ‘e1’, ‘4’, ‘11:32:00’)
select *
from k
order by
case
when Msg = ‘a1’ or Msg = ‘b1’ or Msg = ‘b0’ or Msg = ‘c’ or Msg = ‘d1’ or Msg = ‘e1’ or Msg = ‘f1’ or Msg = ‘g’ or Msg = ‘h’ or Msg = ‘i’ then 1
when Msg = ‘p0’ then 2
when Msg = ‘p1’ then 3
when Msg = ‘qq’ then 4
when Msg = ‘i’ then 5
when Msg = ‘j’ then 6
when Msg = ‘k0’ then 7
when Msg = ‘k1’ then 8
when Msg = ‘l’ then 9
when Msg = ‘l1’ then 10
else 11
end, Msg
但是这段代码没有返回所需的输出。如果'id'列中只有一组值,则根据代码中给出的条件对'Msg'列进行相应的排序。但是在样本输入中,'id'列(1,2和3)中有3组不同的值。所以当我执行上面的代码时,对整个数据进行排序。很明显,因为我没有给出'id'列分组的条件。有没有解决方案来达到预期的效果? (另外我试图通过使用游标来获取输入表中的每一行,但即使在那里我也无法正确地将条件放入循环中)。任何帮助都会有用。
答
这是你在找什么:
select * from k order by id, Msg
基本上,你已经有了“身份证”列的值,你要订购两列,第一个ID,然后该消息的结果;意味着id中具有相同值的行将按Msg的值排序。