日期:2014-05-16 浏览次数:20756 次
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-31 17:18:29
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([用户编号] varchar(3),[本月水量] int)
insert [tb]
select '001',2 union all
select '002',5 union all
select '003',2 union all
select '004',4 union all
select '005',7 union all
select '006',100 union all
select '009',20 union all
select '011',20 union all
select '022',0 union all
select '023',0 union all
select '024',6
--------------开始查询--------------------------
DECLARE @i INT
SELECT @i=COUNT(1) FROM dbo.tb
SELECT TOP 1* FROM (SELECT TOP (CAST(CEILING(@i*0.8) AS INT)) * FROM tb ORDER BY 本月水量)t ORDER BY t.本月水量 desc
----------------结果----------------------------
/* 用户编号 本月水量
---- -----------
009 20
(1 行受影响)
*/
create table bu
(用户编号 varchar(10),本月水量 int)
insert into bu
select '001',2 union all
select '002',5 union all
select '003',2 union all
select '004',4 union all
select '005',7 union all