select top 50 PERCENT .... 表示选择前50%的记录,比如一共有40条记录,则只读取前20条
select top 100 PERCENT .... 表示选择前100%的记录,相当于select * from ....
select top 100 PERCENT .... 表示选择前100%的记录,相当于select * from ....
补充:结合order by ,with ties 关键字
加上order by 字段,会选出按照字段排序的纪录,另外加上with ties 关键字,当order by 字段相等时候,2笔都会秀出来!
例如:
DECLARE @TEST TABLE (Cid INT,Cdate DATETIME)
INSERT INTO @TEST SELECT 1,'2005-10-1' UNION ALL
SELECT 2,'2005-10-2' UNION ALL
SELECT 3,'2005-10-3' UNION ALL
SELECT 4,'2005-10-3' UNION ALL
SELECT 5,'2005-10-5' UNION ALL
SELECT 1,'2005-10-6' UNION ALL
SELECT 7,'2005-10-7' UNION ALL
SELECT 8,'2005-10-7'
select top 30 percent * from @TEST order by Cdate
select top 30 percent with ties * from @TEST order by Cdate
/*
(8 row(s) affected)
Cid Cdate
----------- ------------------------------------------------------
1 2005-10-01 00:00:00.000
2 2005-10-02 00:00:00.000
3 2005-10-03 00:00:00.000
(3 row(s) affected)
Cid Cdate
----------- ------------------------------------------------------
1 2005-10-01 00:00:00.000
2 2005-10-02 00:00:00.000
3 2005-10-03 00:00:00.000
4 2005-10-03 00:00:00.000
(4 row(s) affected)
*/
加上order by 字段,会选出按照字段排序的纪录,另外加上with ties 关键字,当order by 字段相等时候,2笔都会秀出来!
例如:
DECLARE @TEST TABLE (Cid INT,Cdate DATETIME)
INSERT INTO @TEST SELECT 1,'2005-10-1' UNION ALL
SELECT 2,'2005-10-2' UNION ALL
SELECT 3,'2005-10-3' UNION ALL
SELECT 4,'2005-10-3' UNION ALL
SELECT 5,'2005-10-5' UNION ALL
SELECT 1,'2005-10-6' UNION ALL
SELECT 7,'2005-10-7' UNION ALL
SELECT 8,'2005-10-7'
select top 30 percent * from @TEST order by Cdate
select top 30 percent with ties * from @TEST order by Cdate
/*
(8 row(s) affected)
Cid Cdate
----------- ------------------------------------------------------
1 2005-10-01 00:00:00.000
2 2005-10-02 00:00:00.000
3 2005-10-03 00:00:00.000
(3 row(s) affected)
Cid Cdate
----------- ------------------------------------------------------
1 2005-10-01 00:00:00.000
2 2005-10-02 00:00:00.000
3 2005-10-03 00:00:00.000
4 2005-10-03 00:00:00.000
(4 row(s) affected)
*/
分类:
数据库