The input and the desired output is shown below
输入和所需输出如下所示
Input
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
Desired output:
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
The code is below
代码如下
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
But this code is not returning the desired output. In case if there is only one set of values in the ‘id’ column then it sorts the ‘Msg’ column accordingly as per the condition given in the code. But in the sample input there are 3 different sets of values in ‘id’ column (1,2 and 3). So when I execute the above code the sorting is done for the whole data. It is obvious because I did not give any conditions for grouping by ‘id’ column. Is there any solution to achieve the desired result? (Also I tried to fetch each line in the input table by using cursors but even there I was not able to put the conditions properly in the loop). Any help would be useful.
但是这段代码没有返回所需的输出。如果'id'列中只有一组值,则它会根据代码中给出的条件对'Msg'列进行相应的排序。但在样本输入中,'id'列(1,2和3)中有3组不同的值。因此,当我执行上述代码时,将对整个数据进行排序。很明显,因为我没有给'id'列分组提供任何条件。有没有解决方案可以达到预期的效果? (另外我试图通过使用游标来获取输入表中的每一行,但即使在那里我也无法在循环中正确地设置条件)。任何帮助都会有用。
1 个解决方案
#1
0
This is what you are looking for:
这就是你要找的东西:
select * from k order by id, Msg
Basically, you already have the value of 'id' column and you want to order the result by two columns, first the Id and then the Msg; meaning the rows that have the same value in id, will be sorted by the value of Msg.
基本上,您已经拥有'id'列的值,并且您希望按两列排序结果,首先是Id,然后是Msg;意味着id中具有相同值的行将按Msg的值排序。
#1
0
This is what you are looking for:
这就是你要找的东西:
select * from k order by id, Msg
Basically, you already have the value of 'id' column and you want to order the result by two columns, first the Id and then the Msg; meaning the rows that have the same value in id, will be sorted by the value of Msg.
基本上,您已经拥有'id'列的值,并且您希望按两列排序结果,首先是Id,然后是Msg;意味着id中具有相同值的行将按Msg的值排序。