å¦ä½è®©SELECT æ¥è¯¢ç»æé¢å¤å¢å èªå¨éå¢åºå·
å¦ææ°æ®è¡¨æ¬èº«å¹¶ä¸å
å«èªå¨éå¢ç¼å·çå段æ¶ï¼è¦æä¹åæè½å¤è®©SELECTæ¥è¯¢ç»æé¢å¤å¢å èªå¨éå¢åºå·å¢ï¼ä¸åäºç§æ¹æ³ä¾æ¨åèï¼
USE test;
GO
/* æ¹æ³ä¸*/
SELECT åºå·= (SELECT COUNT(客æ·ç¼å·) FROM å®¢æ· AS LiMing WHERE LiMing.客æ·ç¼å·<= Chang.客æ·ç¼å·), 客æ·ç¼å·,å
¬å¸å称 FROM å®¢æ· AS Chang ORDER BY 1;
GO
/* æ¹æ³äº: 使ç¨SQL Server 2005 ç¬æçRANK() OVER () è¯æ³*/
SELECT RANK() OVER (ORDER BY 客æ·ç¼å· DESC) AS åºå·, 客æ·ç¼å·,å
¬å¸å称FROM客æ·;
GO
/* æ¹æ³ä¸*/
SELECT åºå·= COUNT(*), LiMing.客æ·ç¼å·, LiMing.å
¬å¸å称 FROM 客æ·
AS LiMing, å®¢æ· AS Chang WHERE LiMing.客æ·ç¼å·>= Chang.客æ·ç¼å·
GROUP BY LiMing.客æ·ç¼å·, LiMing.å
¬å¸å称 ORDER BY åºå·;
GO
/* æ¹æ³å建ç«ä¸ä¸ªãèªå¨ç¼å·ãçå段ï¼ç¶åå°æ°æ®æ°å¢è³ä¸ä¸ªåºåæ§æåæ°æ®è¡¨ï¼ç¶åç±è¯¥åºåæ§æåæ°æ®è¡¨ä¸ï¼å°æ°æ®éååºæ¥ï¼æåå é¤è¯¥åºåæ§æåæ°æ®è¡¨*/
SELECT åºå·= IDENTITY(INT,1,1),管é,ç¨åºè¯è¨,讲å¸,èµå INTO #LiMing FROM é®å¸è°æ¥ä¸;
GO
SELECT * FROM #LiMing;
GO
DROP TABLE #LiMing;
GO
/*æ¹æ³äºä½¿ç¨ SQL Server 2005 ç¬æçROW_NUMBER() OVER () è¯æ³æé
CTE ï¼ä¸è¬æ°æ®è¡¨
表达å¼ï¼å°±æ¯ WITH é£æ®µè¯æ³ï¼éååºå·2 ~ 4 çæ°æ®*/
WITH
æåºåçå¾ä¹¦ AS (SELECT ROW_NUMBER() OVER (ORDER BY 客æ·ç¼å· DESC) AS åºå·,
客æ·ç¼å·,å
¬å¸å称 FROM 客æ·) SELECT * FROM æåºåçå¾ä¹¦ WHERE åºå· BETWEEN 2 AND 4;
GO