T-Sql选择* 30％到40％之间

Question

• How do I write a T-SQL Stored Procedure that lets me select percentages of rows between X% and Y%?

如何编写T-SQL存储过程,让我选择X%和Y%之间的行百分比?

• So basically I would want to select the rows between 30 PERCENT and 40 PERCENT.....

所以基本上我想要选择30 PERCENT和40 PERCENT之间的行.....

I know that you can do the following, but obviously that doesn't let met specify a set of rows between 2 percentages.

我知道您可以执行以下操作,但显然不会让我们在2个百分比之间指定一组行。

SELECT TOP 50 PERCENT * FROM tblAssets 

Help greatly appreciated.

非常感谢。

2 个解决方案

#1

Updated Answer

declare @NumRecords int
SELECT @NumRecords = COUNT(*) FROM tblAssets;

With Vals As
(
SELECT tblAssets.AssetId ...
, ROW_NUMBER() OVER ( order by tblAssets.AssetId) as RN
  FROM tblAssets
)

SELECT  tblAssets.AssetId ...
FROM vals 
Where RN between 0.3*@NumRecords and 0.4*@NumRecords

I've updated my answer as there were 2 problems with my original answer below

我已经更新了我的答案,因为我的原始答案有两个问题

1. Performance - It was beaten by the nested TOP solution

性能 - 它被嵌套的TOP解决方案击败

2. Accuracy - There is an unexpected aspect of NTILE that I was not aware of

准确性 - 我不知道有一个意想不到的NTILE方面

If the number of rows in a partition is not divisible by integer_expression, this will cause groups of two sizes that differ by one member. Larger groups come before smaller groups in the order specified by the OVER clause. For example if the total number of rows is 53 and the number of groups is five, the first three groups will have 11 rows and the two remaining groups will have 10 rows each.

如果分区中的行数不能被integer_expression整除,则会导致两个大小的组相差一个成员。较大的组按OVER子句指定的顺序位于较小的组之前。例如,如果总行数为53且组数为5,则前三组将具有11行,其余两组将分别具有10行。

I got the following values comparing with the nested TOP solution.

与嵌套的TOP解决方案相比,我得到以下值。

SET STATISTICS IO ON
SET STATISTICS TIME ON;

DECLARE @NumRecords int
SELECT @NumRecords = COUNT(*) FROM [master].[dbo].[spt_values];

WITH Vals As
(
SELECT  [number]
, ROW_NUMBER() OVER ( order by [number]) as RN
  FROM [master].[dbo].[spt_values]
)

SELECT [number] FROM vals Where RN
 BETWEEN 0.30*@NumRecords AND 0.40*@NumRecords

Gives

Table 'spt_values'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'spt_values'。扫描计数1,逻辑读取8,物理读取0,预读取读取0,lob逻辑读取0,lob物理读取0,lob预读读取0。

Table 'spt_values'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'spt_values'。扫描计数1,逻辑读取5,物理读取0,预读取读取0,lob逻辑读取0,lob物理读取0,lob预读读取0。

SELECT TOP 25 PERCENT [number] FROM
(
SELECT TOP 40 PERCENT  [number]
FROM  [master].[dbo].[spt_values]
ORDER BY [number]  ASC
) TOP40
ORDER BY [number] DESC

Gives

Table 'Worktable'. Scan count 1, logical reads 4726, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'工作台'。扫描计数1,逻辑读取4726,物理读取0,预读取读取0,lob逻辑读取0,lob物理读取0,lob预读读取0。

Table 'spt_values'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

表'spt_values'。扫描计数1,逻辑读取8,物理读取0,预读取读取0,lob逻辑读取0,lob物理读取0,lob预读读取0。

Original Answer

With Vals As
(
SELECT tblAssets.AssetId ...
, NTILE (100)  OVER ( order by tblAssets.AssetId) as Pct
  FROM tblAssets 
)

SELECT * FROM vals Where Pct between 30 and 40

#2

I came up with this myself.......

我自己想出来了......

SELECT TOP 40 *  
    INTO #TOP40
    FROM CCDtblAssets

 SELECT * FROM #TOP40
WHERE ASSETID NOT IN   
(SELECT TOP 30 ASSETID FROM #TOP40)

Although I do like Martins Answer.

虽然我喜欢马丁斯答案。