34,591
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[t_fz](
[bh] [int] NOT NULL,
[mc] [varchar](100) NOT NULL,
[je] [money] NULL,
CONSTRAINT [PK_t_fz] PRIMARY KEY CLUSTERED
(
[bh] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
go
insert t_fz ([bh],[mc],[je]) values ( 1,'耗材费',97.59)
insert t_fz ([bh],[mc],[je]) values ( 2,'中草药费 ',98.24)
insert t_fz ([bh],[mc],[je]) values ( 3,'中成药费 ',404.03)
insert t_fz ([bh],[mc],[je]) values ( 4,'西药费 ',24500.00)
insert t_fz ([bh],[mc],[je]) values ( 6,'其他草药',161.00)
insert t_fz ([bh],[mc],[je]) values ( 20,'挂号费',0.50)
insert t_fz ([bh],[mc],[je]) values ( 30,'床位费',10239.00)
insert t_fz ([bh],[mc],[je]) values ( 50,'冷暖费',66.00)
insert t_fz ([bh],[mc],[je]) values ( 60,'诊查费',47.00)
insert t_fz ([bh],[mc],[je]) values ( 70,'护理',298.70)
insert t_fz ([bh],[mc],[je]) values ( 80,'治疗费',70.00)
insert t_fz ([bh],[mc],[je]) values ( 120,'检查费',60.00)
insert t_fz ([bh],[mc],[je]) values ( 180,'心电图',34590.00)
insert t_fz ([bh],[mc],[je]) values ( 190,'动态心电图',120.00)
insert t_fz ([bh],[mc],[je]) values ( 210,'化验费',2154.00)
insert t_fz ([bh],[mc],[je]) values ( 260,'造影',119.00)
insert t_fz ([bh],[mc],[je]) values ( 330,'车费',2.50)
insert t_fz ([bh],[mc],[je]) values ( 340,'其他费',3.00)
;WITH a(bh,mc,je,rn) AS (
SELECT bh,mc,je,
ROW_NUMBER() OVER(ORDER BY bh)
FROM t_fz
),
b(分组号,bh,mc,je,rn,组合计,余额) AS (
SELECT 1,
bh, mc,
CASE WHEN je > 9999 THEN 9999 ELSE je END,
rn,
CASE WHEN je > 9999 THEN 9999 ELSE je END,
CASE WHEN je > 9999 THEN je - 9999 ELSE 0 END
FROM a
WHERE rn = 1
UNION ALL -- 余额非零,新分组
SELECT 分组号 + 1,
bh, mc,
CASE WHEN 余额 > 9999 THEN 9999 ELSE 余额 END,
rn,
CASE WHEN 余额 > 9999 THEN 9999 ELSE 余额 END,
CASE WHEN 余额 > 9999 THEN 余额 - 9999 ELSE 0 END
FROM b
WHERE 余额 <> 0
UNION ALL -- 余额为零,下一条明细
SELECT CASE WHEN b.组合计 < 9999 THEN b.分组号 ELSE b.分组号 + 1 END,
a.bh, a.mc,
CASE WHEN b.组合计 + a.je > 9999 THEN 9999 - b.组合计 ELSE a.je END,
a.rn,
CASE WHEN b.组合计 + a.je > 9999 THEN 9999 ELSE b.组合计 + a.je END,
CASE WHEN b.组合计 + a.je > 9999 THEN b.组合计 + a.je - 9999 ELSE 0 END
FROM b
JOIN a
ON a.rn = b.rn + 1
WHERE b.余额 = 0
)
SELECT *
FROM b
分组号 bh mc je rn 组合计 余额
----------- ----------- ---------- --------------------- -------------------- --------------------- ---------------------
1 1 耗材费 97.59 1 97.59 0.00
1 2 中草药费 98.24 2 195.83 0.00
1 3 中成药费 404.03 3 599.86 0.00
1 4 西药费 9399.14 4 9999.00 15100.86
2 4 西药费 9999.00 4 9999.00 5101.86
3 4 西药费 5101.86 4 5101.86 0.00
3 6 其他草药 161.00 5 5262.86 0.00
3 20 挂号费 0.50 6 5263.36 0.00
3 30 床位费 4735.64 7 9999.00 5503.36
4 30 床位费 5503.36 7 5503.36 0.00
4 50 冷暖费 66.00 8 5569.36 0.00
4 60 诊查费 47.00 9 5616.36 0.00
4 70 护理 298.70 10 5915.06 0.00
4 80 治疗费 70.00 11 5985.06 0.00
4 120 检查费 60.00 12 6045.06 0.00
4 180 心电图 3953.94 13 9999.00 30636.06
5 180 心电图 9999.00 13 9999.00 20637.06
6 180 心电图 9999.00 13 9999.00 10638.06
7 180 心电图 9999.00 13 9999.00 639.06
8 180 心电图 639.06 13 639.06 0.00
8 190 动态心电图 120.00 14 759.06 0.00
8 210 化验费 2154.00 15 2913.06 0.00
8 260 造影 119.00 16 3032.06 0.00
8 330 车费 2.50 17 3034.56 0.00
8 340 其他费 3.00 18 3037.56 0.00