问题描述

sql语句优化,很着急，在线等，sql如下

SELECT
t1.user_id AS user_id,
t1.user_name AS user_name,
t1.serv_num AS serv_num,
t1.create_date AS create_date,
t1.connect_type AS connect_type,
t1.login_device AS login_device,
t1.login_time AS login_time,
(
SELECT
timestampdiff(DAY, t1.create_date, now())
) AS reg_day,
ifnull(t2.totalcount, 0) AS buy_day,
ifnull(
count(DISTINCT c.order_no),
0
) AS buy_orders,
ifnull(sum(c.price * c.num), 0) AS buy_mon,
ifnull(
count(DISTINCT s.order_no),
0
) AS def_orders,
ifnull(sum(s.refund_price), 0) AS def_mon,
ifnull(count(ar.area_name), 0) AS addr_flag,
ifnull(count(shopc.cart_id), 0) AS cart_flag,
ar.area_name AS area_name
FROM
g_customer t1
LEFT JOIN g_serial_records t ON t1.user_id = t.user_id
AND pay_time IS NOT NULL
AND pay_time != '1900-01-01 00:00:00'
LEFT JOIN g_consume_records c ON c.user_id = t1.user_id
AND c.order_no = t.order_no
LEFT JOIN g_refund_records s ON t1.user_id = s.user_id
AND (s.state != '6')
AND t.order_no = s.order_no
AND s.product_id = c.product_id
AND s.sku_no = c.sku_no
LEFT JOIN (
SELECT
t2.user_id AS user_id,
count(t2.user_id) AS totalcount
FROM
(
SELECT
user_id,
date_format(create_date, '%y%m%d')
FROM
g_serial_records
WHERE
pay_time > '1900-01-01 00:00:00'
GROUP BY
date_format(create_date, '%y%m%d'),
user_id
) t2
GROUP BY
t2.user_id
) t2 ON t.user_id = t2.user_id
LEFT JOIN g_shopping_cart shopc ON shopc.user_id = t1.user_id
LEFT JOIN (
SELECT
ad.area_name,
ad.user_id
FROM
(
SELECT
min(newa.create_date),
newa.user_id,
newa.area_name
FROM
g_customer_addr newa
GROUP BY
newa.user_id
) ad
GROUP BY
ad.user_id
) ar ON ar.user_id = t1.user_id
GROUP BY
t1.user_id

解决方案

自己顶帖，就看看那几个left 外连接的查询如何优化