sql - 基于OR子句的SQL计算列

如果我有以下疑问：

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"    |

最佳答案

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'

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

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条件必须寻找这种可能性。

关于sql - 基于OR子句的SQL计算列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39006649/