sql中根据另一列

Question

，我有以下表格的信息挑整行的具体信息：

Location_id | Date_1 | Ex_Start | EX_End | Condition | Price 
------------+------------+-------------+-----------------------+------- 
    L_1  | 23-JUL-16 | 08-JUN-16 | 25-JUL-16 | EX  | 109 
    L_1  | 23-JUL-16 | 28-JUL-16 | 31-JUL-16 | Non_EX | 109 
    L_1  | 24-JUL-16 | 08-JUN-16 | 25-JUL-16 | EX  | 89 
    L_1  | 24-JUL-16 | 28-JUL-16 | 31-JUL-16 | Non_EX | 89 
    ...   ...   ...   ...  ...  
    L_2  | 23-JUL-16 | 24-JUL-16 | 15-AUG-16 | Non_EX | 99 
    L_2  | 23-JUL-16 | 26-OCT-16 | 29-JAN-17 | Non_EX | 99 
    L_2  | 24-JUL-16 | 24-JUL-16 | 15-AUG-16 | EX  | 79 
    L_2  | 24-JUL-16 | 26-OCT-16 | 29-JAN-17 | Non_EX | 79 
    ...   ...   ...   ...  ... 

“DATE_1”是从当前日期到明年的同一天。 对于“条件”栏，“EX”表示“Date_1”落入EX期间（在“EX_Start”和“Ex_End”之间） 对于“条件”列，“Non_EX”表示它不落入EX期间，和“EX”意味着它落入 我想要在所有Location_id中选择特定的Date_1信息，并且如果在同一日期下每个位置有一个“Ex”，条件将返回“EX”，否则它将返回“Non_EX 。”

例如该表将返回：

Location_id | Date_1 | Condition | Price 
------------+------------+-------------+----------------------- 
    L_1  | 23-JUL-16 | EX   | 109 
    L_1  | 24-JUL-16 | EX   | 89 
    ...   ...   ...   ... 
    L_2  | 23-JUL-16 | Non_EX  | 99 
    L_2  | 24-JUL-16 | EX   | 79 
    ...   ...   ...   ... 

感谢

Answer 1

THI！ s会为你的情况工作，请尝试。

SELECT location,date1, min(condition),Price 
from location group by location, date1,Price 

更新以包括价格也。

Answer 2

如果位置和DATE_1的每个组合的价格是相同的，遵循@Prashant Majhwar。但是，如果在价格可能各自EX和之间不同Non_EX，你可能要沿着

WITH 
Data_Raw (location_ID, date_1, ex_Start, ex_End, condition, price) AS (
    SELECT 'L_1', '23-JUL-16', '08-JUN-16', '25-JUL-16', 'EX', 109 FROM DUAL UNION ALL 
    SELECT 'L_1', '23-JUL-16', '28-JUL-16', '31-JUL-16', 'Non_EX', 110 FROM DUAL UNION ALL 
    SELECT 'L_1', '24-JUL-16', '08-JUN-16', '25-JUL-16', 'EX', 89 FROM DUAL UNION ALL 
    SELECT 'L_1', '24-JUL-16', '28-JUL-16', '31-JUL-16', 'Non_EX', 90 FROM DUAL UNION ALL 
    SELECT 'L_2', '23-JUL-16', '24-JUL-16', '15-AUG-16', 'Non_EX', 99 FROM DUAL UNION ALL 
    SELECT 'L_2', '23-JUL-16', '26-OCT-16', '29-JAN-17', 'Non_EX', 99 FROM DUAL UNION ALL 
    SELECT 'L_2', '24-JUL-16', '24-JUL-16', '15-AUG-16', 'EX', 79 FROM DUAL UNION ALL 
    SELECT 'L_2', '24-JUL-16', '26-OCT-16', '29-JAN-17', 'Non_EX', 80 FROM DUAL 
), 
Data (location_ID, date_1, ex_Start, ex_End, condition, price) AS (
SELECT 
    location_Id, TO_DATE(date_1, 'DD-MON-RR'), TO_DATE(ex_Start, 'DD-MON-RR') 
    , TO_DATE(ex_End, 'DD-MON-RR'), condition, price 
FROM Data_Raw 
) 
SELECT DISTINCT 
    M.location_ID 
    , TO_CHAR (M.date_1, 'DD-MON-RR') date_1 
    , M.condition 
    , D.price 
FROM 
    (SELECT 
    location_ID 
    , date_1 
    , MIN(condition) condition 
    FROM Data 
    GROUP BY location_ID, date_1 
) M 
JOIN Data D 
    ON M.location_ID = D.location_ID 
    AND M.date_1 = D.date_1 
    AND M.condition = D.condition 
ORDER BY location_id, date_1 
; 

尝试：

• 使用子查询来放大通缉条件,
• JOIN回到您的数据拉价格（并允许这样做与其他列UMNS以及 - 通过你的问题的标题的建议），并
• DISTINCT s到避免重复行的Non_EX

如有意见，并因为这需要调整/进一步的细节。