求编写SQL查询：计算ECOM_DATA表订单7658状态15到20的天数差

Hey there! Let's break down how to get the number of days between when order 7658 switched from status 15 to status 20.

Approach #

The core idea is simple:

• Grab the activity date of the record where the order is in status 15
• Grab the activity date of the record where the order is in status 20
• Calculate the difference between these two dates

Basic SQL Query (Works for Most Databases) #

Since your sample data only has one entry per status for the order, here's a straightforward self-join query:

-- For Oracle (using native date subtraction)
SELECT 
    (TRUNC(stat20.STAT_DATE_ACT) - TRUNC(stat15.STAT_DATE_ACT)) AS days_between_status_change
FROM 
    ECOM_DATA stat15
INNER JOIN 
    ECOM_DATA stat20 
    ON stat15.ORD_NO = stat20.ORD_NO
WHERE 
    stat15.ORD_NO = 7658
    AND stat15.INT_ORD_LINE_STAT_EXP = 15
    AND stat20.INT_ORD_LINE_STAT_EXP = 20;

If you're using MySQL, swap in the DATEDIFF function:

-- For MySQL
SELECT 
    DATEDIFF(stat20.STAT_DATE_ACT, stat15.STAT_DATE_ACT) AS days_between_status_change
FROM 
    ECOM_DATA stat15
INNER JOIN 
    ECOM_DATA stat20 
    ON stat15.ORD_NO = stat20.ORD_NO
WHERE 
    stat15.ORD_NO = 7658
    AND stat15.INT_ORD_LINE_STAT_EXP = 15
    AND stat20.INT_ORD_LINE_STAT_EXP = 20;

Handling Multiple Entries for the Same Status #

If your table might have duplicate status entries (e.g., the order was reverted to status 15 before moving to 20), adjust the query to pick the most recent status 15 date and the first status 20 date that follows it:

-- Oracle example with aggregation
SELECT 
    (TRUNC(stat20.first_status_20_date) - TRUNC(stat15.last_status_15_date)) AS days_between_status_change
FROM 
    (
        SELECT 
            ORD_NO, 
            MAX(STAT_DATE_ACT) AS last_status_15_date
        FROM ECOM_DATA
        WHERE ORD_NO = 7658 AND INT_ORD_LINE_STAT_EXP = 15
        GROUP BY ORD_NO
    ) stat15
INNER JOIN 
    (
        SELECT 
            ORD_NO, 
            MIN(STAT_DATE_ACT) AS first_status_20_date
        FROM ECOM_DATA
        WHERE ORD_NO = 7658 AND INT_ORD_LINE_STAT_EXP = 20
        GROUP BY ORD_NO
    ) stat20 
    ON stat15.ORD_NO = stat20.ORD_NO;

Expected Result #

For your sample data, all these queries will return 5—the exact number of days between 11-OCT-17 and 16-OCT-17.

内容的提问来源于stack exchange，提问作者R Srini