SQL使用回退列进行匹配

Say I have a table in an sql database like

假设我在sql数据库中有一个表

name    age  shoesize
---------------------
tom      20      NULL
dick   NULL         4
harry    30         5

and I want an SQL statement that selects names that have age == X, or as a fallback, if no such names exist, use those with shoe size == Y. In other words, in this table, for X=20,Y=4 I should only get 'tom', while for X=25,Y=4 I should get only 'dick'. I can't do that with

我想要一个选择年龄== X的名称的SQL语句，或者如果不存在这样的名称，则使用后备，使用鞋号== Y的那些。换句话说，在此表中，对于X = 20，Y = 4我应该只得到'tom'，而对于X = 25，Y = 4我应该只得到'dick'。我不能这样做

SELECT name FROM table WHERE age = 20 OR shoe size = 4;

because that will select both tom and dick. I'm currently using

因为那将选择汤姆和迪克。我正在使用

SELECT COALESCE ((SELECT name FROM tab WHERE age = 20),(SELECT name FROM tab WHERE shoesize = 4));

but is there a neater way? Also using coalesce like this doesn't allow me to get the whole row - i.e. I can't use SELECT * FROM tab, I can only select a single name.

但有更简洁的方式吗？同样使用coalesce这样也不允许我获取整行 - 即我不能使用SELECT * FROM选项卡，我只能选择一个名称。

1 个解决方案

#1

You can use ORDER BY and FETCH FIRST 1 ROW ONLY or some similar clause:

您只能使用ORDER BY和FETCH FIRST 1 ROW或类似的子句：

SELECT name
FROM tab
ORDER BY (CASE WHEN age = X THEN 1
               WHEN shoesize = Y THEN 2
               ELSE 3
          END)
FETCH FIRST 1 ROW ONLY;

Some databases spell FETCH FIRST 1 ROW ONLY like LIMIT or TOP or even something else.

有些数据库只能像LIMIT或TOP或其他东西一样拼写FETCH FIRST 1 ROW。

#1