问题

以下是翻译好的部分：

数据包括在指定日期服用补充剂（A、AB、B、C）的 ID：

补充剂 AB 包括补充剂 A 和 B。

我需要对每个 ID 进行分类，并在满足以下条件时获取 ID 服用 B 或 AB 或两者一起的日期：

1. 必须在服用 C（或 C IS NULL）之前服用 B 或 AB。
2. 如果服用 B，则需要在服用 B 之前或同时服用 A 或 AB。如果在服用 B 之前服用了 AB，则日期为 AB 的服用日期。当在服用 B 之前服用了 A 时，日期为 B 的服用日期。

此外，有时一个 ID 可以同时服用多种补充剂，这也是可以的。例如 ID 11 同时服用 A 和 AB。

当我查看表格时，很容易应用，但将其转化为逻辑形式不像我预期的那样有效。

这是我的尝试，不幸的是给出了错误的结果：

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

期望的结果：

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;

答案1

得分: 1

您的条件并不是100％清晰，但看起来您可以这样做
```tsql
SELECT *，
    CASE
        WHEN (LEAST(AB，B) <= C OR C IS NULL)
            AND (LEAST(AB，A) <= B OR B IS NULL)
        THEN LEAST(AB，B)
    END AS 补充日期
FROM 表1
ORDER BY ID;

在较旧版本的SQL Server中，您不能使用LEAST。因此，您需要使用大量的CASE或IIF

SELECT *，
    CASE
        WHEN (IIF(AB IS NOT NULL，IIF(B IS NOT NULL AND B < AB，B，AB)，B) <= C OR C IS NULL)
            AND (IIF(AB IS NOT NULL，IIF(A IS NOT NULL AND A < AB，A，AB)，A) <= B OR B IS NULL)
        THEN IIF(AB IS NOT NULL，IIF(B IS NOT NULL AND B < AB，B，AB)，B)
    END AS 补充日期
FROM 表1
ORDER BY ID;

db<>fiddle

<details>
<summary>英文:</summary>

Your conditions aren&#39;t 100% clear, but it looks like you can do it like this
```tsql
SELECT *,
    CASE
      WHEN (LEAST(AB, B) &lt;= C OR C IS NULL)
       AND (LEAST(AB, A) &lt;= B OR B IS NULL)
      THEN LEAST(AB, B)
    END AS SupplementDate
FROM table1
ORDER BY ID;

In older versions of SQL Server, you can't use LEAST. So you need to do a bunch of CASE or IIF

SELECT *,
    CASE
      WHEN (IIF(AB IS NOT NULL, IIF(B IS NOT NULL AND B < AB, B, AB), B) <= C OR C IS NULL)
       AND (IIF(AB IS NOT NULL, IIF(A IS NOT NULL AND A < AB, A, AB), A) <= B OR B IS NULL)
      THEN IIF(AB IS NOT NULL, IIF(B IS NOT NULL AND B < AB, B, AB), B)
    END AS SupplementDate
FROM table1
ORDER BY ID;

db<>fiddle