根据参数显示列

Question

我正在尝试编写一个SQL脚本，显示基于用户参数的列。我找不到一个可以接受的答案。

这是我想做的一个简化版本(在OracleDB):

SELECT Table_car.purchase_date  AS PurchaseDate,
Table_car.sale_date AS SaleDate,
CASE WHEN :userinput <> 999 THEN (SELECT Table_car.car_brand FROM Table_car WHERE Table_car.car_brand = :userinput ) 
ELSE (SELECT Table_car.car_brand FROM Table_car) 
END AS CarBrand
FROM Table_car

我想要的伪代码可以是

if(user_input = 999)
show_all_values_of(Table_car.car_brand );
else
show_only_some_values_of(Table_car.car_brand, user_input);

这是预期的结果:

if user_input <> 999 (if is 1, for example)
| PurchaseDate | SaleDate   | CarBrand |
+--------------+------------+----------+
|  03/12/2018  | 09/10/2021 |    1     |
----------------------------------------
|  13/06/2011  | 20/11/2021 |    1     |
----------------------------------------
|  22/01/2020  | 09/12/2021 |    1     |
----------------------------------------
else (if user input is 999)
| PurchaseDate | SaleDate   | CarBrand |
+--------------+------------+----------+
|  03/12/2018  | 09/10/2021 |    1     |
----------------------------------------
|  13/06/2001  | 20/11/2021 |    5     |
----------------------------------------
|  22/01/2020  | 09/12/2021 |    1     |
----------------------------------------
|  03/12/2018  | 09/10/2021 |    3     |
----------------------------------------
|  13/06/2012  | 10/10/2020 |    9     |
----------------------------------------
|  22/01/2020  | 09/12/2020 |    2     |
----------------------------------------

Answer 1

您可以将逻辑放入where子句中，并使用or;比如:

SELECT Table_car.purchase_date AS PurchaseDate,
Table_car.sale_date AS SaleDate,
Table_car.car_brand AS CarBrand
FROM   Table_car
WHERE  :userinput = 999 OR Table_car.car_brand = :userinput

如果你的列是空的，你可能需要额外的逻辑以一种特殊的方式来处理它，但它看起来不应该是这样的。如果你需要where中的其他条件，记得在上面的逻辑周围加上括号，以确保它被解释为你想要的;例如

WHERE  (:userinput = 999 OR Table_car.car_brand = :userinput)
AND ... something else

作为题外话，您可能需要考虑使用表别名来减少一点输入。