如何在SQL Server中更新每位患者使用实验室服务的次数?

Question

我有一个名为" lab_master(visit_rank,pid) "的表。Visit_rank:访问次数Pid:为患者id

我想更新"visit_rank"以就诊次数为值，每位患者从第1次就诊开始。我试过下面的SQL代码，但它是不正确的。

DECLARE cursor_visit_rank CURSOR FOR
SELECT pid
FROM   lab_master
DECLARE @visit_rank INT
DECLARE @pid VARCHAR(50)
OPEN cursor_visit_rank
FETCH NEXT FROM cursor_visit_rank INTO @pid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @visit_rank = 0
UPDATE lab_master
SET    visit_rank = @visit_rank + 1
WHERE  pid = @pid
FETCH NEXT FROM cursor_visit_rank INTO @pid
END
CLOSE cursor_visit_rank
DEALLOCATE cursor_visit_rank  

Answer 1

您想要计算每个人的访问次数并存储它吗?试试这个:

DECLARE cursor_visit_rank CURSOR FOR
SELECT pid FROM lab_master order by pid
DECLARE @visit_rank INT
declare @thisid int = 0
DECLARE @pid VARCHAR(50)
OPEN cursor_visit_rank
FETCH NEXT FROM cursor_visit_rank INTO @pid
WHILE @@FETCH_STATUS = 0
BEGIN
if @thisid <> @pid
set @visit_rank = 0
set @thisid = @pid
SET @visit_rank += 1
UPDATE lab_master SET visit_rank = @visit_rank where pid = @pid
FETCH NEXT FROM cursor_visit_rank INTO @pid
END
CLOSE cursor_visit_rank
DEALLOCATE cursor_visit_rank

Answer 2

每当您发现自己在编写游标时，都应该质疑自己的假设。几乎没有实际需要游标的情况，否则会非常慢。

相反，您可以使用窗口函数以基于集合的方式完成此操作。

;
WITH cte AS (
SELECT *,
count = COUNT(*) OVER (PARTITION BY lm.pid)
FROM lab_master lm
)
UPDATE cte
SET visit_rank = count;

或者您可以执行自连接

UPDATE lm
SET visit_rank = lm2.pid
FROM lab_master lm
CROSS APPLY (
SELECT
count = COUNT(*)
FROM lab_master lm2
WHERE lm2.pid = lm.pid
) lm2;