sql讲解

SELECT
    s.flight_no,
    s.car_logo_no,
    s.time,
    s.is_upload_naga,
    s.totalExtractedGoodsCount,
    s.alreadyExtractedGoodsCount,
     (
        CASE
      WHEN (s.totalExtractedGoodsCount-s.alreadyExtractedGoodsCount)=0 THEN '1'
      WHEN s.actual_arrive_time is not null THEN '2'
      WHEN s.arrive_time > NOW() THEN '3'
      ELSE '4' END
     ) taskType,
     (
      CASE
      when (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount) > 0 
          THEN (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount)
        ELSE 0 END
     ) unAlreadyExtractedGoodsCount
    FROM
    (SELECT * FROM (SELECT
        a.flight_no,
        a.car_logo_no,
        (
            CASE
            WHEN a.actual_arrive_time IS NOT NULL THEN
                a.actual_arrive_time
            ELSE
                a.arrive_time
            END
        ) time,
      a.arrive_time,
      a.actual_arrive_time,
      a.is_transfer,
        b.last_extract_time,
        b.is_dropped,
        b.is_upload_naga,
        b.is_back_up_car_logo,
        (
            SELECT
                sum(c.total_waybill_count)
            FROM
                tdop_bulk_shipping_extract_task c
            WHERE
                c.car_logo_no = b.car_logo_no
            AND c.transit_depot_no = '755X'
            AND c.slice_id = '55'
        AND c.state = '1'
        AND c.is_transfer = (
                CASE WHEN EXISTS (
                                    SELECT 1 FROM tdop_bulk_shipping_extract_task d
                                    WHERE d.slice_id = '55'
                    AND d.car_logo_no = b.car_logo_no
                    AND d.transit_depot_no = '755X'
                    AND d.state = '1' AND d.is_transfer = 0
                            ) THEN 0 ELSE 1 END
               )
        ) totalExtractedGoodsCount,
        (
            SELECT
                count(*)
            FROM
                tdop_extract_goods_waybill w
            WHERE
                w.car_logo_no = b.car_logo_no
            AND w.transit_depot_no = '755X'
            AND w.slice_id = '55'
            AND w. STATUS = 3
        ) alreadyExtractedGoodsCount
    FROM
        tdop_bulk_shipping_extract_task a,
        tdop_bulk_shipping_extract_car_logo b
    WHERE
    a.transit_depot_no = '755X'
    AND a.slice_id = '55'
    AND b.transit_depot_no = '755X'
    AND b.slice_id = '55'
    AND a.car_logo_no = b.car_logo_no
    AND a.state = '1'
    GROUP BY
        a.flight_no,
        a.car_logo_no) t
    WHERE
    CASE
    WHEN t.is_dropped = 1 THEN t.last_extract_time BETWEEN DATE_SUB(NOW(),INTERVAL 8 HOUR) and NOW()
    WHEN t.is_back_up_car_logo = 1 THEN t.time BETWEEN DATE_SUB(NOW(),INTERVAL 12 HOUR) AND NOW()
    ELSE t.arrive_time BETWEEN  DATE_SUB(NOW(),INTERVAL 6 HOUR) AND DATE_ADD(NOW(),INTERVAL 2 HOUR) END ) s

SELECT
  s.flight_no,
  s.car_logo_no,
  s.time,
  s.is_upload_naga,
    s.alreadyExtractedGoodsCount,
  s.totalExtractedGoodsCount,
 (
    CASE
  WHEN (s.totalExtractedGoodsCount-s.alreadyExtractedGoodsCount)=0 THEN '1'
  WHEN s.actual_arrive_time is not null THEN '2'
  WHEN s.arrive_time > NOW() THEN '3'
  ELSE '4' END
 ) taskType,
 (
  CASE
  when (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount) > 0 THEN (s.totalExtractedGoodsCount - s.alreadyExtractedGoodsCount)
    ELSE 0 END
 ) unAlreadyExtractedGoodsCount
FROM
(SELECT *,(
        SELECT
            count(*)
        FROM
            tdop_extract_goods_waybill w
        WHERE
            w.car_logo_no = t.car_logo_no
        AND w.transit_depot_no = '010RA'
        AND w.slice_id = '51'
        AND w. STATUS = 3
    ) alreadyExtractedGoodsCount,
    (
    SELECT
            sum(c.total_waybill_count)
        FROM
            tdop_bulk_shipping_extract_task c
        WHERE
            c.car_logo_no = t.car_logo_no
        AND c.transit_depot_no = '010RA'
        AND c.slice_id = '51'
    AND c.state = '1'
    AND c.is_transfer = (
            CASE WHEN EXISTS (
                                SELECT 1 FROM tdop_bulk_shipping_extract_task d
                                WHERE d.slice_id = '51'
                AND d.car_logo_no = t.car_logo_no
                AND d.transit_depot_no = '010RA'
                AND d.state = '1' AND d.is_transfer = 0
                        ) THEN 0 ELSE 1 END
           )
)totalExtractedGoodsCount
FROM (SELECT
    a.flight_no,
    a.car_logo_no,
    (
        CASE
        WHEN a.actual_arrive_time IS  NULL THEN
            a.arrive_time
        ELSE
            a.actual_arrive_time
        END
    ) time,
  a.arrive_time,
  a.actual_arrive_time,
  a.is_transfer,
    b.last_extract_time,
    b.is_dropped,
    b.is_upload_naga,
    b.is_back_up_car_logo
FROM
    tdop_bulk_shipping_extract_task a,
    tdop_bulk_shipping_extract_car_logo b
WHERE
a.transit_depot_no = '010RA'
AND a.slice_id = '51'
AND b.transit_depot_no = '010RA'
AND b.slice_id = '51'
AND a.car_logo_no = b.car_logo_no
AND a.state = '1'
AND
CASE
WHEN b.is_dropped = 0 AND b.is_back_up_car_logo =0 THEN a.arrive_time BETWEEN  DATE_SUB(NOW(),INTERVAL 6 HOUR) AND DATE_ADD(NOW(),INTERVAL 2 HOUR)
WHEN b.is_dropped = 1 THEN b.last_extract_time BETWEEN DATE_SUB(NOW(),INTERVAL 8 HOUR) and NOW()
ELSE a.arrive_time BETWEEN DATE_SUB(NOW(),INTERVAL 12 HOUR) AND NOW() END
GROUP BY
    a.flight_no,
    a.car_logo_no) t) s