用户定义标量函数中的前1个 [英] Top 1 in user defined scalar function

问题描述

ALTER FUNCTION [dbo].[UFHRMS_GetTaxRate]
(
        @TaxPerY decimal(18,2)
)

RETURNS decimal(18,2)
AS    
    BEGIN
        DECLARE @RESULT decimal (18,2)
        
        select @RESULT= Max(tp. TAX_PERCENTAGE) from TAX_PERCENTAGES tp
        where
        tp.starting_amount <=@taxpery
        
        
     
        if
         @TaxPerY = 0
         set @Result=0
    
        
    RETURN     @RESULT
    END

该查询工作正常.但我想做前1名的经验，我想要这样的查询

this query works fine. but I want todo experience with top 1 I want the query like this

select @RESULT= top 1 (tp. TAX_PERCENTAGE) from TAX_PERCENTAGES tp
    where
    tp.starting_amount <=@taxpery

回覆


[edit]已添加代码块-OriginalGriff [/edit]

Reply


[edit]Code blocks added - OriginalGriff[/edit]

推荐答案

使用以下代码

Hi,

use below code

with t1 as (select ROW_NUMBER() over (order by tp.TAX_PERCENTAGE) as t2,* from TAX_PERCENTAGES tp where tp.starting_amount <=@taxpery)
set @RESULT = (select t1.TAX_PERCENTAGE FROM t1 where t2=1)

希望这能解决您的问题.

谢谢
-Amit.

hope this will solve your problem.

thanks
-Amit.

这篇关于用户定义标量函数中的前1个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！