如
--NTILE分类函数
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
DROP TABLE tba
END
GO
CREATE TABLE tba
(
ID INT IDENTITY(1,1) NOT NULL,
Total INT
)
GO
INSERT INTO tba
SELECT '100' UNION
SELECT '101' UNION
SELECT '102' UNION
SELECT '103' UNION
SELECT '104' UNION
SELECT '105' UNION
SELECT '106' UNION
SELECT '107' UNION
SELECT '108' UNION
SELECT '109' UNION
SELECT '110'
GO
SELECT ID,
Total,
CASE NTILE(4) OVER (ORDER BY Total DESC)
WHEN 1 THEN '25%'
WHEN 2 THEN '25%-50%'
WHEN 3 THEN '50%-75%'
WHEN 4 THEN '75%-100%' END AS Level
FROM tba
/*
ID Total Level
11 110 25%
10 109 25%
9 108 25%
8 107 25%-50%
7 106 25%-50%
6 105 25%-50%
5 104 50%-75%
4 103 50%-75%
3 102 50%-75%
2 101 75%-100%
1 100 75%-100%
*/
这样比直接用SQL语句写要方便得多。
