-- 创建临时表并插入数据

CREATE TABLE #temp (

    name VARCHAR(100),

    age INT

);

INSERT INTO #temp (name, age)

VALUES

    ('张三', 19),

    ('李四', 10);

-- 查询结果

SELECT

    t.name,

    (t.age - ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY t.name, t.age) + 1) AS age

FROM

    #temp t

ORDER BY

    t.name, t.age;

-- 删除临时表

DROP TABLE #temp;

首先创建了一个名为 #temp 的临时表，并插入了原始数据。然后使用 SELECT 查询语句，通过计算 ROW_NUMBER 和使用与子查询获取所需结果，并按 name 和 age 排序。

with a as(

select 'A' AS A,19 AS B,10 min_qty

UNION ALL

select 'B' AS A,10 AS B,10

)

select a.a,MIN_QTY,SEQ,a.b

,case when MIN_QTY * rnk < b THEN MIN_QTY ELSE b-MIN_QTY*(rnk-1) END AS NEW_QTY

from (

select a.a,a.b,MIN_QTY,count(MIN_QTY) over ( partition by a ) as SEQ,b.number + 1 as rnk

from  a

CROSS join master..spt_values b

where b.number * a.MIN_QTY <= a.B and b.Type = 'P'

) a