英文:
Postgres OR operator - Only check second condition if no match on first condition
问题
以下是您要翻译的内容：
有没有办法编写一个OR条件，以便仅在第一个条件不返回结果时才检查第二个条件？
例如：
CREATE TABLE some_table (
column_a VARCHAR(10),
column_b VARCHAR(10));
INSERT INTO some_table
VALUES ('A1', 'B1'), ('A2', 'B2');
我希望这个查询只返回A1、B1这一行，因为它使用第一个条件找到了匹配项：
SELECT * FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2';
我希望这个查询只返回A2、B2这一行，因为第一个条件没有匹配项：
SELECT * FROM some_table
WHERE column_a = 'X1' OR column_b = 'B2';
或者也许有一种不使用OR运算符的解决方案？
我知道您可以执行多个查询，就像在这个示例中一样，但是当查询更大时，这并不理想。
另一种可能的解决方案可能是使用动态SQL，这样您可以将查询的大部分（不包括第二个条件）存储为变量并执行它，如果没有结果，追加OR条件并再次执行。这将防止在更大的查询中出现代码重复，但我相信一定有更好的解决方案。
英文:
Is there a way to write an OR condition so that the second criteria is only checked if the first criteria yields no results?
For example:
CREATE TABLE some_table (
column_a VARCHAR(10),
column_b VARCHAR(10));
INSERT INTO some_table
VALUES ('A1', 'B1'), ('A2', 'B2');
I'd want this query to return only row with A1, B1, because it found a match using the first criteria:
SELECT * FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2';
I'd want this query to return only row with A2, B2, because there was no match on the first criteria:
SELECT * FROM some_table
WHERE column_a = 'X1' OR column_b = 'B2';
Or perhaps there's a solution that doesnt use the OR operator?
I know you can execute multiple queries like in this example, but that's not ideal when the queries are much larger.
Another possible solution might be to use dynamic SQL, so you can store the majority of the query (without the second or criteria) as a variable execute it, and if no results, append the OR criteria and execute it again. This would prevent duplication of code in larger queries, but I'm sure there must be a better solution.
答案1
得分: 4
Sure, here's the translated code portion:
> 编写一个OR条件，以便仅在第一个条件不产生结果时才检查第二个条件
WHERE谓词子句正确使用了`OR`。如果您只想要结果集中的一行，我们可以通过条件表达式进行排序，首先将符合第一个条件的行排在前面，然后使用`LIMIT`：
SELECT *
FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2'
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END
LIMIT 1
---
如果可能有*多个*符合特定条件的行，则可能需要向`ORDER BY`子句添加另一列，以使排序可预测，例如主键列`id`：
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END, id
LIMIT 1
或者，如果您想保留所有符合给定条件的行，那么您可以使用窗口函数和`FETCH`：
ORDER BY RANK() OVER(ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END)
FETCH FIRST ROW WITH TIES
Please note that the code has been translated, and I have excluded the parts you mentioned not to translate.
英文:
> write an OR condition so that the second criteria is only checked if the first criteria yields no results
The where predicate clause is correctly phrased with OR. If you want just one row in the resultset, we can order by a conditional expression that puts first rows that match the first condition, then limit:
SELECT *
FROM some_table
WHERE column_a = 'A1' OR column_b = 'B2'
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END
LIMIT 1
If there might be several rows matching on a given criteria, then you might want to add another column to the ORDER BY clause to make the sort predictable, say primary key column id:
ORDER BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END, id
LIMIT 1
Alternatively, if you want to retain all rows that match the given criteria, then you use window functions and fetch instead:
ORDER BY RANK() OVER(ORDER BY BY CASE WHEN column_a = 'A1' THEN 1 ELSE 2 END)
FETCH FIRST ROW WITH TIES
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论