sql - 不带 WHILE 循环的查询

问题描述

我们有如下所示的预约表。每个约会都需要分类为“新”或“后续”。首次预约（该患者）后 30 天内的任何预约（针对该患者）均为随访。30 天后，约会再次“新”。30 天内的任何预约都将成为“跟进”。

我目前正在通过键入 while 循环来执行此操作。
如何在没有 WHILE 循环的情况下实现这一目标？

桌子

CREATE TABLE #Appt1 (ApptID INT, PatientID INT, ApptDate DATE)
INSERT INTO #Appt1
SELECT  1,101,'2020-01-05' UNION
SELECT  2,505,'2020-01-06' UNION
SELECT  3,505,'2020-01-10' UNION
SELECT  4,505,'2020-01-20' UNION
SELECT  5,101,'2020-01-25' UNION
SELECT  6,101,'2020-02-12'  UNION
SELECT  7,101,'2020-02-20'  UNION
SELECT  8,101,'2020-03-30'  UNION
SELECT  9,303,'2020-01-28' UNION
SELECT  10,303,'2020-02-02'

标签： sqlsql-servertsqlsql-server-2016

您需要使用递归查询。

30 天的周期从 prev 开始计算（不，如果没有递归/古怪的更新/循环，就不可能做到这一点）。这就是为什么所有使用 only 的现有答案都ROW_NUMBER失败了。

WITH f AS (
  SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY PatientId ORDER BY ApptDate) 
  FROM Appt1
), rec AS (
  SELECT Category = CAST('New' AS NVARCHAR(20)), ApptId, PatientId, ApptDate, rn, startDate = ApptDate
  FROM f
  WHERE rn = 1
  UNION ALL
  SELECT CAST(CASE WHEN DATEDIFF(DAY,  rec.startDate,f.ApptDate) <= 30 THEN N'FollowUp' ELSE N'New' END AS NVARCHAR(20)), 
         f.ApptId,f.PatientId,f.ApptDate, f.rn,
         CASE WHEN DATEDIFF(DAY, rec.startDate, f.ApptDate) <= 30 THEN rec.startDate ELSE f.ApptDate END
  FROM rec
  JOIN f
    ON rec.rn = f.rn - 1
   AND rec.PatientId = f.PatientId
)
SELECT ApptId, PatientId, ApptDate, Category
FROM rec
ORDER BY PatientId, ApptDate;

db<>小提琴演示

输出：

+---------+------------+-------------+----------+
| ApptId  | PatientId  |  ApptDate   | Category |
+---------+------------+-------------+----------+
|      1  |       101  | 2020-01-05  | New      |
|      5  |       101  | 2020-01-25  | FollowUp |
|      6  |       101  | 2020-02-12  | New      |
|      7  |       101  | 2020-02-20  | FollowUp |
|      8  |       101  | 2020-03-30  | New      |
|      9  |       303  | 2020-01-28  | New      |
|     10  |       303  | 2020-02-02  | FollowUp |
|      2  |       505  | 2020-01-06  | New      |
|      3  |       505  | 2020-01-10  | FollowUp |
|      4  |       505  | 2020-01-20  | FollowUp |
+---------+------------+-------------+----------+

这个怎么运作：

f - 获取起点（锚点 - 每个 PatientId）
rec - 递归部分，如果当前值和 prev 之间的差异 > 30，则在 PatientId 的上下文中更改类别和起点
Main - 显示排序的结果集

附录

永远不要在生产中使用此代码！

但是，除了使用 cte 之外，另一个值得一提的选择是使用临时表并在“轮次”中更新

它可以在“单”轮中完成（古怪的更新）：

CREATE TABLE Appt_temp (ApptID INT , PatientID INT, ApptDate DATE, Category NVARCHAR(10))

INSERT INTO Appt_temp(ApptId, PatientId, ApptDate)
SELECT ApptId, PatientId, ApptDate
FROM Appt1;

CREATE CLUSTERED INDEX Idx_appt ON Appt_temp(PatientID, ApptDate);

询问：

DECLARE @PatientId INT = 0,
        @PrevPatientId INT,
        @FirstApptDate DATE = NULL;

UPDATE Appt_temp
SET  @PrevPatientId = @PatientId
    ,@PatientId     = PatientID 
    ,@FirstApptDate = CASE WHEN @PrevPatientId <> @PatientId THEN ApptDate
                           WHEN DATEDIFF(DAY, @FirstApptDate, ApptDate)>30 THEN ApptDate
                           ELSE @FirstApptDate
                      END
    ,Category       = CASE WHEN @PrevPatientId <> @PatientId THEN 'New'
                           WHEN @FirstApptDate = ApptDate THEN 'New'
                           ELSE 'FollowUp' 
                      END
FROM Appt_temp WITH(INDEX(Idx_appt))
OPTION (MAXDOP 1);

SELECT * FROM  Appt_temp ORDER BY PatientId, ApptDate;

db<>fiddle 古怪更新

sql - 不带 WHILE 循环的查询

问题描述

解决方案

附录

推荐阅读