主页 / dba / 问题 / 242398
Accepted
Chessbrain
Asked: 2019-07-09 15:02:34 +0800 CST

将结果输出为 2 个单独的列

  • 772

所以我创建了一个计算字符串数组的平均值和中位数的程序：

CREATE PROCEDURE dbo.spAvg_Median 
    @input nvarchar(20) 
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)

    INSERT INTO @split SELECT value FROM string_split(@input, ',')

    SELECT Average FROM (
        SELECT SUM(number)/COUNT(number) as [Average]
        FROM @split
        ) AS T1
    UNION
    SELECT Median FROM (
        SELECT o.number AS [Median], rn=ROW_NUMBER() OVER (ORDER BY o.number), c.c
        FROM @split AS o
        CROSS APPLY (SELECT c=COUNT(*) FROM @split b) AS c
        ) AS x
        WHERE rn IN ((c + 1)/2, (c + 2)/2)
END
RETURN 0 
GO

但是，当您运行此查询时，您会得到以下结果：

EXEC dbo.spAvg_Median'1,5,9,8,7'

在此处输入图像描述

我希望它输出的是一列中的平均值和旁边另一列中的中值。

UNION 命令在那里，所以它不会像这样将它作为两个不同的查询输出，因为这不是我想要的： 在此处输入图像描述

stored-procedures sql-server-2017

1 个回答

  1. Best Answer

    根据规则找到中位数（如果是偶数个值则为平均值，如果为奇数个值则为中间数）。可以这样做。

    CREATE PROCEDURE dbo.spAvg_Median 
    @input nvarchar(20) 
AS
BEGIN
    declare @split table(id int primary key identity (1,1), number int)
    declare @count int

    INSERT INTO @split SELECT value FROM string_split(@input, ',')
    select @count=count(*) from @split

        SELECT AVG(A.number) as [Average],B.Median
            FROM @split A
        CROSS APPLY
                    (
                    SELECT Median FROM (
                    SELECT
                              o.number AS [Median]
                            , rn=ROW_NUMBER() OVER (ORDER BY o.number ASC)
                    FROM    @split AS o        
                    ) AS x
                    WHERE rn =ROUND(@count/2.0,0)
                    ) B
        GROUP BY B.Median
END
RETURN 0 
GO

    谢谢！

    • 1

相关问题