Oracle SQL-CASE语句在逻辑上不需要时返回多行

Question

我有两个表：

订单

O_NO  O_DATE      O_CO  O_TYPE O_LIST
1653  07/07/2015  12    P      8845

HOL-

HOL_NO  START_DATE    END_DATE   H_LIST
3       29/01/2014    30/06/2014 8845
9       01/10/2014    30/09/2017 8845

因此，根据我目前的查询：

SELECT o.O_NO, o.O_DATE, o.O_CO, o.O_TYPE, h.START_DATE, h.END_DATE, 
       CASE WHEN o.O_DATE BETWEEN h.START_DATE AND h.END_DATE 
            THEN 'Head' 
            ELSE 'Line'
       END AS HOL_TYPE
FROM Order o
LEFT JOIN HOL h ON o.O_LIST = h.H_LIST

这正在输出：

O_NO  O_DATE      O_CO  O_TYPE START_DATE  END_DATE   HOL_TYPE
1653  07/07/2015  12    P      29/01/2014  30/06/2014 Line
1653  07/07/2015  12    P      01/10/2014  30/09/2017 Head

但我的预期输出是：

O_NO  O_DATE      O_CO  O_TYPE START_DATE  END_DATE   HOL_TYPE
1653  07/07/2015  12    P      01/10/2014  30/09/2017 Head

因为O_DATE具体地介于条件和只应返回1行之间。

Answer 1

在join中包含date条件。

SELECT o.O_NO, o.O_DATE, o.O_CO, o.O_TYPE, h.START_DATE, h.END_DATE, 
CASE WHEN o.O_DATE BETWEEN h.START_DATE AND h.END_DATE 
        THEN 'Head' 
        ELSE 'Line'
END AS HOL_TYPE as HOL_TYPE
FROM Order o
LEFT JOIN HOL h ON o.O_LIST = h.H_LIST AND o.O_DATE BETWEEN h.START_DATE AND h.END_DATE