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
sql语句优化,很着急,在线等,sql如下
- 写回答
- 好问题 0 提建议
- 追加酬金
- 关注问题
- 邀请回答
-
1条回答 默认 最新
悬赏问题
- ¥15 有兄弟姐妹会用word插图功能制作类似citespace的图片吗?
- ¥200 uniapp长期运行卡死问题解决
- ¥15 请教:如何用postman调用本地虚拟机区块链接上的合约?
- ¥15 为什么使用javacv转封装rtsp为rtmp时出现如下问题:[h264 @ 000000004faf7500]no frame?
- ¥15 乘性高斯噪声在深度学习网络中的应用
- ¥15 关于docker部署flink集成hadoop的yarn,请教个问题 flink启动yarn-session.sh连不上hadoop,这个整了好几天一直不行,求帮忙看一下怎么解决
- ¥15 深度学习根据CNN网络模型,搭建BP模型并训练MNIST数据集
- ¥15 C++ 头文件/宏冲突问题解决
- ¥15 用comsol模拟大气湍流通过底部加热(温度不同)的腔体
- ¥50 安卓adb backup备份子用户应用数据失败