记录一次SQL查询语句
标签：
记录一次SQL查询语句
CREATE TABLE t_jeff (
2 id int NOT NULL,
3 VINNumber varchar(255) DEFAULT NULL,
4 ChannelID varchar(255) DEFAULT NULL,
5 TimeStamp datetime DEFAULT NULL,
6 PRIMARY KEY (id)
7 ) ;
8 INSERT INTO t_jeff VALUES (‘1‘, ‘Group3‘, ‘3‘, ‘2015-05-27 00:00:00‘);
9 INSERT INTO t_jeff VALUES (‘2‘, ‘Group2‘, ‘5‘, ‘2015-05-23 00:00:00‘);
10 INSERT INTO t_jeff VALUES (‘3‘, ‘Group1‘, ‘4‘, ‘2015-05-25 00:00:00‘);
11 INSERT INTO t_jeff VALUES (‘4‘, ‘Group2‘, ‘3‘, ‘2015-05-29 00:00:00‘);
12 INSERT INTO t_jeff VALUES (‘7‘, ‘Group1‘, ‘2‘, ‘2015-05-30 00:00:00‘);
13
14
15 SELECT * FROM t_jeff
16 --1、VINNumber同组的放一起
17 --2、组之间排序逻辑：按照每组ChannelID最大值 倒序
18 --3、组内排序：按照ChannelID倒序。
19
20 SELECT A.*
21 FROM [dbo].[t_jeff] A
22 JOIN ( SELECT MAX([ChannelID]) AS [MaxChannelID] ,
23 [VINNumber]
24 FROM [dbo].[t_jeff]
25 GROUP BY [VINNumber]
26 ) B ON A.VINNumber = B.VINNumber
27 ORDER BY B.MaxChannelID DESC ,
28 A.ChannelID DESC
29
30
31