基于 OR 子句的 SQL 计算列

Question

如果我有以下查询:

SELECT *
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London';

有什么方法可以添加像 matched 这样的计算列来标记匹配的字段吗？

| number | from     | to     | matched |
|--------|----------|--------|---------|
| 1      | Rome     | Munich | "from"  |
| 2      | New York | London | "to"    |
| 3      | Berlin   | London | "to"    |

Answer 1

SELECT *,
    CASE
       WHEN Flights.From='Rome' THEN 'From'
       WHEN Flights.To='London' THEN 'To'
     END as matched
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London'

您可以创建一个模拟 WHERE 语句逻辑的 CASE 语句。

SELECT *,
    CASE
       WHEN Flights.From IN ('Rome','London','etc.')
             AND Flights.To IN ('Rome','London','etc.') THEN Flights.From || ', ' || Flights.To
       WHEN Flights.From IN ('Rome','London','etc.') THEN Flights.From
       WHEN Flights.To IN ('Rome','London','etc.') THEN Flights.To
     END as MatchedValue
     ,CASE
       WHEN Flights.From IN ('Rome','London','etc.')
             AND Flights.To IN ('Rome','London','etc.') THEN 'Both'
       WHEN Flights.From IN ('Rome','London','etc.') THEN 'From'
       WHEN Flights.To IN ('Rome','London','etc.') THEN 'To'
     END as MatchedDirection
FROM Flights
WHERE Flights.From='Rome' OR Flights.To='London'

当使用 case 语句时，满足的第一个 WHEN 条件将是答案，因此如果您的两个条件都满足并且您希望看到第一个 WHEN 条件必须寻找这种可能性。