英文:
Oracle SQL: Hierarchical WHERE ... IN
问题
我有一个选择语句，在其中我根据某个字段进行查找：
select * from table t
where t.field in ('val1', 'val2', 'val3')
我想要以这样的方式进行子集筛选，如果该列具有值 val1，则返回相应的行，否则应返回具有值 val2 的行，最后返回具有值 val3 的行。可以以某种方式实现有序的where子句吗？
英文:
I have a select-statement, where I finder on a certain field
select * from table t
where t.field in ('val1', 'val2', 'val3')
I would like to subset in such as way that it should return a given row if the column has the value val1, otherwise it should return the row with value val2 and finally the row with val3. So an ordered where-clause so to speak. Is that possible somehow
答案1
得分: 1
获取所有val1行，如果不存在，则获取所有val2行，然后如果不存在，则获取所有val3行，从Oracle 12开始，您可以使用ORDER BY和FETCH FIRST ROW WITH TIES：
SELECT *
FROM 表格
ORDER BY 字段
FETCH FIRST ROW WITH TIES;
如果您的优先级不是升序，则使用CASE表达式：
SELECT *
FROM 表格
ORDER BY
CASE 字段
WHEN 'val1' THEN 1
WHEN 'val2' THEN 2
WHEN 'val3' THEN 3
END
FETCH FIRST ROW WITH TIES;
在较早的版本中，您可以使用RANK分析函数：
SELECT *
FROM (
SELECT t.*,
RANK() OVER (
ORDER BY CASE 字段
WHEN 'val1' THEN 1
WHEN 'val2' THEN 2
WHEN 'val3' THEN 3
END
) AS rnk
FROM 表格 t
)
WHERE rnk = 1;
英文:
To get all the val1 rows and, then if none exist, get all the val2 rows and then, if none exist, all the val3 rows then, from Oracle 12, you can use ORDER BY and FETCH FIRST ROW WITH TIES:
SELECT *
FROM table
ORDER BY field
FETCH FIRST ROW WITH TIES;
If your priorities are not in ascending order then use a CASE expression:
SELECT *
FROM table
ORDER BY
CASE field
WHEN 'val1' THEN 1
WHEN 'val2' THEN 2
WHEN 'val3' THEN 3
END
FETCH FIRST ROW WITH TIES;
In earlier versions, you can use the RANK analytic function:
SELECT *
FROM (
SELECT t.*,
RANK() OVER (
ORDER BY CASE field
WHEN 'val1' THEN 1
WHEN 'val2' THEN 2
WHEN 'val3' THEN 3
END
) AS rnk
FROM table t
)
WHERE rnk = 1;
答案2
得分: 0
COALESCE函数似乎可以帮助您
https://docs.oracle.com/javadb/10.8.3.0/ref/rreffunccoalesce.html
英文:
seems COALESCE function might help you
https://docs.oracle.com/javadb/10.8.3.0/ref/rreffunccoalesce.html
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论