该问题是从Oracle SQL Where Conditions权重扩展而来的，但更笼统（无产品限制，但按产品价值给出一个）。
我有桌子
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');
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('foo', '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) VALUES ('foo', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('foo', '2', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('foo', '3');
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('tmp', '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) VALUES ('tmp', '1', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('tmp', '2', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('tmp', '3');
内容
SELECT * FROM mytable;
和输出
PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS
-------------------- ---------- --------- ---------
bla 0 01-JAN-19 31-JAN-19
bla 1 01-FEB-19 28-FEB-19
bla 2 01-JAN-19
bla 3 01-FEB-19
bla 4 31-JAN-19
bla 5 28-FEB-19
bla 6
bla 7
foo 0 01-JAN-19 31-JAN-19
foo 1 01-FEB-19
foo 2 31-JAN-19
foo 3
tmp 0 01-JAN-19 31-JAN-19
tmp 1 01-JAN-19
tmp 2 28-FEB-19
tmp 3
条件定义
date_act和date_dis定义。date_dis定义但date_act尚未定义时。date_act定义但date_dis尚未定义时。date_act和date_dis没有定义。如果condition_weight 4为true，则左侧（3、2和1）将被忽略。
如果condition_weight 4为false且condition_weight 3为true，则condition_weight 2和condition_weight 1将被忽略。
如果condition_weight 4和3为false，而condition_weight 2为true，则condition_weight 1将被忽略。
如果condition_weight 4、3和2为false，则仅对condition_weight 1进行评估。
问题：如何通过每一只获得一排product_code，其优先级最高，与输入参数date_submit与条件权值定义？
需要输出示例 注意：下表结果是手动创建的（我需要类似的结果，但是没有查询）。
DATE_SUBMIT：='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
4 0 foo 0 01-JAN-19 31-JAN-19 2019/01/15
4 0 tmp 0 01-JAN-19 31-JAN-19 2019/01/15
DATE_SUBMIT：='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
2 1 foo 1 01-FEB-19 2019/02/15
3 2 tmp 2 28-FEB-19 2019/02/15
DATE_SUBMIT：='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
3 2 foo 2 31-JAN-19 2018/12/31
3 2 tmp 2 28-FEB-19 2018/12/31
DATE_SUBMIT：='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
2 1 foo 1 01-FEB-19 2019/12/31
2 1 tmp 1 01-JAN-19 2019/12/31
检查答案https://stackoverflow.com/a/59779653/1410223结果，但是否有可能使查询更简单。
编辑只有priority最高的priority必须是。
使用分配条件权重case when。然后针对每种产品采取最佳排法，使用rank()：
with
d as (select date '2019-02-15' date_submit from dual),
t as (
select m.*, case
when date_act is not null and date_dis is not null then 4
when date_act is null and date_dis is not null then 3
when date_act is not null and date_dis is null then 2
else 1 end condition
from mytable m)
select product_code, priority, date_act, date_dis, condition, date_submit
from (
select t.*, d.*,
rank() over ( partition by product_code order by condition desc ) rnk
from t join d
on date_submit between nvl(date_act, date_submit) and nvl(date_dis, date_submit))
where rnk = 1
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句