代码之家 › 专栏 › 技术社区 › HelenJ

SQL Server 2000:记录的1/3(或2/3)中的字段长度

ranking sql-order-by sorting sql-server sql

3

HelenJ · 技术社区 · 16 年前

使用SQL Server 2000是否有一种更简单/更干净的方法?

SELECT COUNT(*) FROM MyTable

然后我列出某个字段的所有长度:

SELECT LEN(MyText)
FROM MyTable
ORDER BY LEN(MyText) ASC

然后我需要向下滚动1/3的方式。。。并注意值。最后是最后一个值。

我需要找出x、y和z:

 33% of the records have this field with a length under x bytes
 66% of the records have this field with a length under y bytes
100% of the records have this field with a length under z bytes

2 回复 | 直到 16 年前

1

3

Martin Smith 16 年前

在SQL2005中,您可能会为此使用排名函数。在SQL 2000中,我认为您一直在做这样的事情。

DECLARE @RC INT 

CREATE TABLE #lengths
(
id INT IDENTITY(1,1),
[length] INT
)

INSERT INTO #lengths
SELECT LEN(MyText)
FROM MyTable
ORDER BY LEN(MyText) ASC


SET @rc= @@ROWCOUNT

SELECT [length] 
FROM #lengths 
WHERE id IN 
(@rc/3, (2*@rc)/3, @rc)

2

0

Michel de Ruiter 16 年前

SELECT
 x1.l AS Length,
 x1.n      * 1e2 / (SELECT COUNT(*) FROM MyTable) AS [Percent],
 SUM(x2.n) * 1e2 / (SELECT COUNT(*) FROM MyTable) AS CumPercent
FROM (
 SELECT LEN(MyText) AS l, COUNT(*) AS n
 FROM MyTable
 GROUP BY LEN(MyText)
) AS x1
LEFT JOIN (
 SELECT LEN(MyText) AS l, COUNT(*) AS n
 FROM MyTable
 GROUP BY LEN(MyText)
) AS x2
 ON x2.l <= x1.l
GROUP BY x1.l, x1.n
ORDER BY x1.l