数据由ID
名在指定日期服用补充剂(A
、AB
、B
、C
)的人组成,具体如下:
ID | A | AB | B | C |
---|---|---|---|---|
1 | 2021-01-01 | 2021-02-01 | 2021-03-01 | 2021-04-01 |
2 | null | 2021-06-01 | null | 2021-08-01 |
3 | 2021-09-01 | null | 2021-08-01 | null |
4 | null | null | 2021-11-01 | null |
5 | 2021-09-01 | 2021-06-01 | null | null |
6 | 2021-09-01 | null | 2021-11-01 | 2021-11-02 |
7 | 2021-09-01 | 2021-09-01 | 2021-11-01 | 2021-11-02 |
8 | 2021-09-01 | null | 2021-09-01 | 2021-11-02 |
9 | 2021-09-01 | null | 2021-09-01 | 2021-08-02 |
10 | 2021-09-01 | null | 2021-09-01 | 2021-07-02 |
11 | 2021-09-01 | 2021-09-01 | null | null |
12 | 2021-08-01 | 2021-09-01 | null | null |
13 | 2021-08-01 | 2021-09-01 | null | 2021-10-01 |
14 | 2021-08-01 | null | null | 2021-09-01 |
15 | 2021-09-01 | 2021-12-01 | null | 2021-09-01 |
16 | 2021-10-01 | null | null | 2021-09-01 |
Supplement 100 consists of supplements 101 and 102.
如果符合以下条件,我需要对每个ID
个进行分类,并获得取B
或AB
或两者的ID的日期:
- 必须在
C
(或C
IS NULL
)之前 SelectB
或AB
. - 如果取
B
,则需要在B
之前或同时取A
或AB
.如果AB
是在B
之前取的,则AB
是接收日期.如果A
是在B
之前获取的,则B
是接收日期.
此外,有时一个ID可以一起接受补充剂,这也是可以的.例如,带有A
和AB
的ID
11.
当我看一看表时,它很容易应用,但将其放入逻辑中并不像我预期的那样起作用.
这是我的try ,不幸的是得出了错误的结果: 也许有一种完全不同的方法来做这样的事情?
SELECT *,
CASE
WHEN (AB IS NOT NULL AND (AB <= B OR B IS NULL)) THEN AB
WHEN (A IS NOT NULL AND (A <= B OR B IS NULL)) THEN B
ELSE NULL
END AS SupplementDate
FROM Table1
WHERE (B IS NOT NULL OR AB IS NOT NULL)
AND (C IS NULL OR (B IS NOT NULL AND (A IS NOT NULL OR AB IS NOT NULL)))
ORDER BY ID;
数据和方法:db<>fiddle
预期结果:
ID | A | AB | B | C | SupplementDate |
---|---|---|---|---|---|
1 | 2021-01-01 | 2021-02-01 | 2021-03-01 | 2021-04-01 | 2021-02-01 |
2 | null | 2021-06-01 | null | 2021-08-01 | 2021-06-01 |
3 | 2021-09-01 | null | 2021-08-01 | null | null |
4 | null | null | 2021-11-01 | null | null |
5 | 2021-09-01 | 2021-06-01 | null | null | 2021-06-01 |
6 | 2021-09-01 | null | 2021-11-01 | 2021-11-02 | 2021-11-01 |
7 | 2021-09-01 | 2021-09-01 | 2021-11-01 | 2021-11-02 | 2021-09-01 |
8 | 2021-09-01 | null | 2021-09-01 | 2021-11-02 | 2021-09-01 |
9 | 2021-09-01 | null | 2021-09-01 | 2021-08-02 | null |
10 | 2021-09-01 | null | 2021-09-01 | 2021-07-02 | null |
11 | 2021-09-01 | 2021-09-01 | null | null | 2021-09-01 |
12 | 2021-08-01 | 2021-09-01 | null | null | 2021-09-01 |
13 | 2021-08-01 | 2021-09-01 | null | 2021-10-01 | 2021-09-01 |
14 | 2021-08-01 | null | null | 2021-09-01 | null |
15 | 2021-09-01 | 2021-12-01 | null | 2021-09-01 | null |
16 | 2021-10-01 | null | null | 2021-09-01 | null |