如何在Access中转换分区功能以在Microsoft SQL Server中使用? [英] How to convert the Partition function in Access for use in Microsoft SQL Server?

问题描述

我经常使用MS Access，但是最近有人要求我提供有关SQL Server数据库的帮助.我注意到，仅将SQL代码从Access复制到服务器数据库是行不通的.有人可以帮我从Access复制SQL代码的功能到SQL Server吗?

I have used MS Access a lot, but I was recently asked to help with a SQL Server database. I noticed that simply copying the SQL code from Access over to the server database doesn't work. Could someone please help me replicate the functionality of my SQL code from Access into SQL Server?

下面是在Access中工作的SQL视图:

Below is the SQL View that works within Access:

SELECT [LV1]-[LV2] AS Aging, Partition([Aging],1,100,10) AS AgingRange

FROM表1；

Aging是基于表中两列的计算列.我想使用SQL Server对其进行分区，如下所示.

Aging is a calculated column based off of two columns within my table. I want to partition it using SQL Server as seen below.

推荐答案

以下T-SQL标量值函数似乎有效:

The following T-SQL scalar-valued function seems to work:

-- =============================================
-- Author:      Gord Thompson
-- Create date: 2016-07-30
-- Description: like Partition function in Access
-- =============================================
CREATE FUNCTION [dbo].[myPartition] 
(
    @Number int, @Start int, @Stop int, @Interval int
)
RETURNS varchar(max)
AS
BEGIN

    DECLARE @Result varchar(max), @x int, @y int;

    IF @Number is null OR @Start is null OR @Stop is null OR @Interval is null 
            OR @Interval < 1 OR (@Stop - @Start) < 2
    BEGIN
        SELECT @Result = null;
    END
    ELSE
    BEGIN
        IF @Number < @Start 
        BEGIN
            SELECT @Result = ':' + CONVERT(varchar(max), @Start -1);
        END
        ELSE
        BEGIN
            IF @Number > @Stop
            BEGIN
                SELECT @Result = CONVERT(varchar(max), @Stop + 1) + ':';
            END
            ELSE
            BEGIN
                SELECT @x = @Start, @y = @Start + @Interval - 1
                WHILE NOT (@Number >= @x AND @Number <= @y)
                BEGIN
                    SELECT @x = @x + @Interval, @y = @y + @Interval;
                    IF @y > @Stop
                    BEGIN
                        SELECT @y = @Stop;
                    END
                END
                SELECT @Result = CONVERT(varchar(max), @x) + ':' + CONVERT(varchar(max), @y);
            END
        END
    END
    RETURN @Result

END

GO

这篇关于如何在Access中转换分区功能以在Microsoft SQL Server中使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！