Oracle SQL Where条件权重 [英] Oracle SQL Where Conditions weight
问题描述
创建表格！
DROP TABLE mytable;
CREATE TABLE mytable
(
product_code VARCHAR2(20 BYTE) NOT NULL ENABLE,
priority NUMBER NOT NULL ENABLE,
date_act DATE,
date_dis DATE
);
填充表格
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'), TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '2', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '3', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '4', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '5', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('bla', '6');
INSERT INTO mytable (product_code, priority) VALUES ('bla', '7');
现在我只需要使用两个输入参数(或变量)product_code和date_submit的单行，
Now I need only one single row using two input parameters (or variables) product_code and date_submit,
我使用的是OR连接器，但我没有根据date_act和date_dis值定义一些条件权重(确实不包含在mytable中).
I'm using OR connectors, but I neet to define some condition Weight (really is not included in mytable), according to date_act and date_dis values.
如果 condition_weight 4 为true，则左侧(3，2和1)将被忽略.
If condition_weight 4 is true the left (3, 2 and 1) will be ignored.
如果 condition_weight 4 是false，而 condition_weight 3 是true，则 condition_weight 2 和 condition_weight 1 将被忽略.
If condition_weight 4 is false and condition_weight 3 is true , condition_weight 2 and condition_weight 1 will be ignored.
如果 condition_weight 4和3 为false，而 condition_weight 2 为true，则 condition_weight 1 将被忽略.
If condition_weight 4 and 3 are false and condition_weight 2 is true condition_weight 1 will be ignored.
如果 condition_weight 4、3和2 为false，则仅评估 condition_weight 1 .
预期结果...
SELECT * FROM mytable
WHERE product_code = :product_code
AND (
TO_DATE (:date_submit, 'yyyy/mm/dd') BETWEEN date_act AND date_dis --condition_weight 4
OR (TO_DATE (:date_submit, 'yyyy/mm/dd') <= date_dis AND date_act IS NULL) --condition_weight 3
OR (TO_DATE (:date_submit, 'yyyy/mm/dd') >= date_act AND date_dis IS NULL) --condition_weight 2
OR (date_dis IS NULL AND date_act IS NULL) --condition_weight 1
)
AND ROWNUM <= 1
ORDER BY priority DESC
;
有一些想法可以根据要求执行此 SELECT 吗?
Some idea to perform this SELECT with requirements?
推荐答案
我检查了您的Second Image表，并且可以使用...
I checked your Second Image table, and works...
var date_submit varchar2(12);
exec :date_submit := '2019/12/31';
var product_code varchar2(12);
exec :product_code := 'bla';
SELECT resp.*, :date_submit FROM (
SELECT 4 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') BETWEEN date_act AND date_dis
UNION
SELECT 3 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') <= date_dis AND date_act IS NULL
UNION
SELECT 2 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND TO_DATE (:date_submit, 'yyyy/mm/dd') >= date_act AND date_dis IS NULL
UNION
SELECT 1 condition_weight, mytable.priority prio, mytable.* FROM mytable
WHERE product_code = :product_code
AND date_dis IS NULL AND date_act IS NULL
ORDER BY condition_weight DESC, prio DESC
) resp
WHERE ROWNUM <= 1
;
输出
2019/01/15
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 0 bla 0 01-JAN-19 31-JAN-19 2019/01/15
2019/02/15
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 1 bla 1 01-FEB-19 28-FEB-19 2019/02/15
2018/12/31
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
3 5 bla 5 28-FEB-19 2018/12/31
2019/12/31
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
2 3 bla 3 01-FEB-19 2019/12/31
这篇关于Oracle SQL Where条件权重的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
