时间戳

首页 > 解决方案 > 仅选择一行，按值字段，排序

sql - 仅选择一行，按值字段，排序

问题描述

此问题从Oracle SQL Where 条件权重扩展而来，但更笼统（没有产品限制，但按产品值给出）。

我有一张桌子

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

条件定义

如果 condition_weight 4 为真，则左侧（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 为假，则只会评估 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必须是。

标签： sqloraclesql-order-byrownum

解决方案

使用 分配条件权重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

推荐阅读