问题描述:

Say I have a table in an sql database like

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

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.

网友答案:

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

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.

相关阅读:
Top