问在sql中创建减法矩阵

EN

DECLARE @t TABLE(
  id_nbr INT
 ,t1 INT
 ,t2 INT
 ,t3 INT
 ,t4 INT
 ,t5 INT
 ,t6 INT
);

INSERT INTO @t VALUES (1111,10,20,30,40,50,40), (2222,40,10,50,20,70,90);


DECLARE @SearchID INT = 2222;
DECLARE @TimeIdxFrom INT = 1;
DECLARE @TimeIdxTo INT = 5;

WITH cteBase AS(
  SELECT id_nbr, ROW_NUMBER() OVER (PARTITION BY id_nbr ORDER BY (SELECT 1)) rn, times
  FROM (
  SELECT id_nbr, t1, t2, t3, t4, t5, t6
    FROM @t t) t
    UNPIVOT(
      times FOR cTime IN (t1, t2, t3, t4, t5, t6)
    ) u
),
cteMinMax AS(
  SELECT id_nbr, MIN(times) MinTime, MAX(times) MaxTime
    FROM cteBase
    WHERE rn >= @TimeIdxFrom
      AND rn <= @TimeIdxTo
    GROUP BY id_nbr
)
SELECT  --*,
        cbMax.rn - cbMin.rn AS TimeSpan,
        cbMax.times - cbMin.times AS ValDiff
  FROM cteMinMax AS cmm
  JOIN cteBase AS cbMin ON cmm.id_nbr = cbMin.id_nbr AND cbMin.times = cmm.MinTime
  JOIN cteBase AS cbMax ON cmm.id_nbr = cbMax.id_nbr AND cbMax.times = cmm.MaxTime
  WHERE cbMax.id_nbr = @SearchID;

CREATE TABLE  dbo.T 
(
    id_nbr  INT,
    t1  INT,
    t2  INT,
    t3  INT,
    t4  INT,
    t5  INT,
    t6  INT
)
GO
INSERT INTO dbo.T   (id_nbr, t1, t2, t3, t4, t5, t6)
VALUES
(  1111,  10, 20, 30, 40, 50, 40  ),
(2222,  40, 10, 50, 20, 70, 90  )

GO

CREATE FUNCTION dbo.find_max_and_time(@Id INT)
RETURNS TABLE 
AS RETURN 
WITH Desce AS 
(
    SELECT TOP 1 WITH TIES a.id_nbr , b.*
    FROM dbo.T a
    CROSS APPLY 
    (
        SELECT 1, a.T1 UNION ALL
        SELECT 2, a.T2 UNION ALL
        SELECT 3, a.T3 UNION ALL
        SELECT 4, a.T4 UNION ALL
        SELECT 5, a.T5 UNION ALL
        SELECT 6, a.T6  
    ) as b(N,Val)
    WHERE id_nbr = @Id OR @Id IS NULL
    ORDER BY ROW_NUMBER() OVER(PARTITION BY id_nbr ORDER BY Val DESC)   
), Asce AS 
(
    SELECT TOP 1 WITH TIES a.id_nbr,b.*
    FROM dbo.T a
    CROSS APPLY 
    (
        SELECT 1, a.T1 UNION ALL
        SELECT 2, a.T2 UNION ALL
        SELECT 3, a.T3 UNION ALL
        SELECT 4, a.T4 UNION ALL
        SELECT 5, a.T5 UNION ALL
        SELECT 6, a.T6  
    ) as b(N,Val)   
    WHERE id_nbr = @Id OR @Id IS NULL
    ORDER BY ROW_NUMBER() OVER(PARTITION BY id_nbr ORDER BY Val ASC)    
)
SELECT 
    a.id_nbr, 
    a.Val - b.Val   as Val,
    a.N - b.N   as N
FROM 
    Desce a
INNER JOIN 
    Asce b
ON a.id_nbr = b.id_nbr

GO

SELECT * FROM dbo.find_max_and_time (1111)

DROP TABLE dbo.T 
DROP FUNCTION dbo.find_max_and_time