关于rank、dense_rank、ROW_NUMBER及OVER(PARTITION BY)、OVER(ORDER BY)的一些用法

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