我必须与Oracle进行查询,以通过某些字段优先级找到客户字段为:
NUM_CIN,NUM_PASS,BIRTHDATE,NAME
查询的优先级是:
1)首先是num_cin如果没有结果,则
2)如果没有结果,则num_pass
3)如果没有结果,则按名称 生日
4)仅按名称
如何使用Oracle SQL?
您可以在4个条件下结合4个查询,并以优先级附加虚拟列。这样的东西:
WITH all_results AS (
SELECT t1.*,
1 priority
FROM CUSTOMER_TABLE t1
WHERE NUM_CIN = ...
UNION
SELECT t2.*,
2 priority
FROM CUSTOMER_TABLE t2
WHERE NUM_PASS = ...
UNION
SELECT t3.*,
3 priority
FROM CUSTOMER_TABLE t3
WHERE NAME = ... AND BIRTHDATE = ...
UNION
SELECT t4.*,
4 priority
FROM CUSTOMER_TABLE t4
WHERE NAME = ...
)
SELECT *
FROM all_results
WHERE priority = (SELECT min(priority) FROM all_results)