CREATE TABLE t_harry (
id int NOT NULL,
Number varchar(
255)
DEFAULT NULL,
ChannelID varchar(
255)
DEFAULT NULL,
TimeStamp datetime DEFAULT NULL,
PRIMARY KEY (id)
) ;
INSERT INTO t_harry
VALUES (
'1',
'Group3',
'3',
'2015-05-27 00:00:00');
INSERT INTO t_harry
VALUES (
'2',
'Group2',
'5',
'2015-05-23 00:00:00');
INSERT INTO t_harry
VALUES (
'3',
'Group1',
'4',
'2015-05-23 00:00:00');
INSERT INTO t_harry
VALUES (
'4',
'Group2',
'3',
'2015-05-29 00:00:00');
INSERT INTO t_harry
VALUES (
'7',
'Group1',
'2',
'2015-05-30 00:00:00');
SELECT B.
* FROM t_harry B
LEFT JOIN
(
SELECT Number,
max(ChannelID)
[MaxID] FROM t_harry
GROUP BY Number
) A ON B.
Number=A.
Number
ORDER BY A.MaxID
DESC , B.ChannelID
DESC
SELECT rank()
OVER(
ORDER BY TimeStamp)
[Rank],
dense_rank() OVER(
ORDER BY TimeStamp)
[dense_rank],
ROW_NUMBER() OVER(
ORDER BY TimeStamp)
[Row_Number],
ROW_NUMBER() OVER(PARTITION
BY Number ORDER BY TimeStamp)
[Row_Number],
* FROM t_harry
ORDER BY max(Channelid)
OVER (PARTITION
BY Number)
DESC, channelid
DESC
DROP TABLE t_harry
转载于:https://www.cnblogs.com/harryhan1983/p/4543915.html
相关资源：数据结构—成绩单生成器
