SQL Server 2008 - 查询以分数格式获取结果 [英] SQL Server 2008 - Query to get result in fraction format

问题描述

我有一个包含如下数据的表格:

I have a table which contains data like this:

MinFormat(int)  MaxFormat(int)  Precision(nvarchar)
   -2              3             1/2

精度值只能是 1/2、1/4、1/8、1/16、1/32、1/64.

The values in precision can be 1/2, 1/4, 1/8, 1/16, 1/32, 1/64 only.

现在我希望查询结果为 -

Now I want result from query as -

 -2
 -3/2 
 -1 
 -1/2 
  0 
  1/2 
  1 
  3/2  
  2 
  5/2 
  3

有什么查询可以得到如下结果吗?

Any query to get the result as follows?

想法是根据精度值创建基于最小边界(MinFomrat col 值为整数)到最大边界(MaxFormat Col 值为整数)的结果.

Idea is to create result based onMinimum boundary (MinFomrat col value which is integer) to Maximum boundary (MaxFormat Col value which is integer) accordingly to the precision value.

因此，在上面的例子中，value 应该从 -2 开始，并根据精度值 (1/2) 生成下一个值，直到达到 3

Hence, in above example, value should start from -2 and generate the next values based on the precision value (1/2) till it comes to 3

推荐答案

注意这仅适用于 Precision 1/1、1/2、1/4、1/8、1/16、1/32 和 1/64

Note this will only work for Precision 1/1, 1/2, 1/4, 1/8, 1/16, 1/32 and 1/64

DECLARE @t table(MinFormat int, MaxFormat int, Precision varchar(4))
INSERT @t values(-2, 3, '1/2')

DECLARE @numerator INT, @denominator DECIMAL(9,7)
DECLARE @MinFormat INT, @MaxFormat INT

-- put a where clause on this to get the needed row

SELECT @numerator   = 1,
       @denominator = STUFF(Precision, 1, charindex('/', Precision), ''),
       @MinFormat = MinFormat,
       @MaxFormat = MaxFormat
FROM @t


;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
SELECT top(cast((@MaxFormat- @MinFormat)  / (@numerator/@denominator) as int) + 1) 
CASE WHEN val % 1 = 0 THEN cast(cast(val as int) as varchar(10))
     WHEN val*2 % 1 = 0 THEN cast(cast(val*2 as int) as varchar(10)) + '/2'
     WHEN val*4 % 1 = 0 THEN cast(cast(val*4 as int) as varchar(10)) + '/4'
     WHEN val*8 % 1 = 0 THEN  cast(cast(val*8 as int) as varchar(10)) + '/8'
     WHEN val*16 % 1 = 0 THEN cast(cast(val*16 as int) as varchar(10)) + '/16'
     WHEN val*32 % 1 = 0 THEN cast(cast(val*32 as int) as varchar(10)) + '/32'
     WHEN val*64 % 1 = 0 THEN cast(cast(val*64 as int) as varchar(10)) + '/64'
END
FROM tally
CROSS APPLY
(SELECT @MinFormat +(N-1) *(@numerator/@denominator) val) x

这篇关于SQL Server 2008 - 查询以分数格式获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！