-- 守护礼

SELECT
y.*,
IFNULL( y1.num, 0 ),
IFNULL( y2.num, 0 ),
IFNULL( y3.num, 0 ),
IFNULL( y3.num / y2.num, 0 ),
IFNULL( y4.num, 0 ),
IFNULL( y5.num, 0 ),
IFNULL( y5.num / y2.num, 0 )
FROM
(
SELECT
t.store_id,
t.store_name,
t1.are,
t1.super_name
FROM
hd_stores_info t,
hd_stores_super t1
WHERE
t.store_id = t1.store_id
AND t.is_signed = ‘1’
) y
LEFT JOIN (
SELECT
t.store_id,
count( DISTINCT t.vin_num ) num
FROM
rescue_info t
WHERE
t.type = ‘01’
AND t.create_time >= ‘2021-11-01 00:00:00’
GROUP BY
t.store_id
) y1 ON y.store_id = y1.store_id
LEFT JOIN (
SELECT
t4.store_id,
count( t4.vin_num ) num
FROM
(
SELECT
t2.vin_num,
SUBSTRING_INDEX( GROUP_CONCAT( t3.store_id ORDER BY t3.to_store_time DESC ), ‘,’, 1 ) store_id
FROM
hd_activity t,
hd_activity_coupon_relation t1,
sub_coupon_code t2,
hd_maintain_record t3
WHERE
t.activity_id = t1.activity_id
AND t1.coupon_id = t2.coupon_id
AND t2.vin_num = t3.vin_num
AND t.activity_id = ‘WDAC0048’
AND t3.maintain_type IN ( ‘定保’, ‘首保’ )
GROUP BY
t2.vin_num
) t4
GROUP BY
t4.store_id
) y2 ON y.store_id = y2.store_id
LEFT JOIN (
SELECT
z.store_id,
count( DISTINCT z1.vin_num ) num
FROM
(
SELECT
t2.vin_num,
SUBSTRING_INDEX( GROUP_CONCAT( t3.store_id ORDER BY t3.to_store_time DESC ), ‘,’, 1 ) store_id
FROM
hd_activity t,
hd_activity_coupon_relation t1,
sub_coupon_code t2,
hd_maintain_record t3
WHERE
t.activity_id = t1.activity_id
AND t1.coupon_id = t2.coupon_id
AND t2.vin_num = t3.vin_num
AND t.activity_id = ‘WDAC0048’
AND t3.maintain_type IN ( ‘定保’, ‘首保’ )
GROUP BY
t2.vin_num
) z,
hd_maintain_record z1
WHERE
z.vin_num = z1.vin_num
AND z1.to_store_time >= ‘2021-11-01 00:00:00’
GROUP BY
z.store_id
) y3 ON y.store_id = y3.store_id
LEFT JOIN (
SELECT
z.store_id,
count( DISTINCT z1.vin_num ) num
FROM
(
SELECT
t2.vin_num,
SUBSTRING_INDEX( GROUP_CONCAT( t3.store_id ORDER BY t3.to_store_time DESC ), ‘,’, 1 ) store_id
FROM
hd_activity t,
hd_activity_coupon_relation t1,
sub_coupon_code t2,
hd_maintain_record t3
WHERE
t.activity_id = t1.activity_id
AND t1.coupon_id = t2.coupon_id
AND t2.vin_num = t3.vin_num
AND t.activity_id = ‘WDAC0048’
AND t3.maintain_type IN ( ‘定保’, ‘首保’ )
GROUP BY
t2.vin_num
) z,
hd_maintain_record z1
WHERE
z.vin_num = z1.vin_num
AND z1.to_store_time >= ‘2021-11-01 00:00:00’
AND z1.maintain_type IN ( ‘保险理赔’, ‘钣喷自费’ )
GROUP BY
z.store_id
) y4 ON y.store_id = y4.store_id
LEFT JOIN (
SELECT
t4.store_id,
count( DISTINCT t4.vin_num ) num
FROM
(
SELECT
t2.vin_num,
t3.store_id
FROM
hd_activity t,
hd_activity_coupon_relation t1,
sub_coupon_code t2,
hd_maintain_record t3
WHERE
t.activity_id = t1.activity_id
AND t1.coupon_id = t2.coupon_id
AND t2.vin_num = t3.vin_num
AND t.activity_id = ‘WDAC0048’
AND t2.code_state = ‘01’
GROUP BY
t2.vin_num
) t4
GROUP BY
t4.store_id
) y5 ON y.store_id = y5.store_id;