SELECT marketId AS marketId branchName AS branchName marketName AS marketName COUNT DISTINCT uploadPeopleCnt AS uploadPeopleCnt SUM uploadOldCnt AS uploadOldCnt SUM putOldCnt AS putOldCnt COUNT DIS
以下是修改后的SQL：
SELECT
marketId AS marketId,
branchName AS branchName,
marketName AS marketName,
COUNT( DISTINCT uploadPeopleCnt ) AS uploadPeopleCnt,
SUM( uploadOldCnt ) AS uploadOldCnt,
SUM( putOldCnt ) AS putOldCnt,
COUNT( DISTINCT couponsCnt ) AS couponsCnt,
COUNT( DISTINCT couponsPeopleCnt ) AS couponsPeopleCnt,
COUNT( userCouponsCnt ) AS userCouponsCnt,
COUNT( DISTINCT userCouponsPeopleCnt ) AS userCouponsPeopleCnt,
COUNT( DISTINCT userCouponsPeopleCnt ) AS orderPeopleCnt,
COUNT( DISTINCT orderCnt ) AS orderCnt,
ROUND( SUM( orderTotalMoney ), 2 ) AS orderTotalMoney,
SUM( retrieveOldCnt ) AS retrieveOldCnt
FROM
(
SELECT
market_id AS marketId,
branch_name AS branchName,
market_name AS marketName,
NULL AS uploadPeopleCnt,
0 AS uploadOldCnt,
0 AS putOldCnt,
NULL AS couponsCnt,
NULL AS couponsPeopleCnt,
NULL AS userCouponsCnt,
NULL AS userCouponsPeopleCnt,
NULL AS orderCnt,
0 AS orderTotalMoney,
0 AS retrieveOldCnt
FROM
dwd_old_for_new_all_detail
WHERE
old_goods_type = 2 UNION ALL
SELECT
market_id,
branch_name,
market_name,
member_id,
old_goods_num,
0 AS putOldCnt,
NULL AS couponsCnt,
NULL AS couponsPeopleCnt,
NULL AS userCouponsCnt,
NULL AS userCouponsPeopleCnt,
NULL AS orderCnt,
0 AS orderTotalMoney,
0 AS retrieveOldCnt
FROM
dwd_old_for_new_all_detail
WHERE
old_goods_type = 1
AND data_type = 1
) a
LEFT JOIN (
SELECT
market_id,
branch_name,
market_name,
old_goods_num AS putOldCnt
FROM
dwd_old_for_new_all_detail
WHERE
old_goods_type = 1
AND data_type = 4
AND shipping_document_status IN ( 5, 6 )) b ON a.marketId = b.market_id
LEFT JOIN (
SELECT
market_id,
branch_name,
market_name,
old_goods_num AS retrieveOldCnt
FROM
dwd_old_for_new_all_detail
WHERE
old_goods_type = 1
AND data_type = 4
AND shipping_document_status IN ( 1, 2, 3, 4, 5, 6 )) c ON a.marketId = c.market_id
LEFT JOIN ( SELECT market_id, branch_name, market_name, get_coupon_code AS couponsCnt, member_id AS couponsPeopleCnt FROM dwd_old_for_new_all_detail WHERE old_goods_type = 1 AND data_type = 2 ) d ON a.marketId = d.market_id
LEFT JOIN ( SELECT market_id, branch_name, market_name, use_coupon_code AS userCouponsCnt, buyer_id AS userCouponsPeopleCnt FROM dwd_old_for_new_all_detail WHERE old_goods_type = 1 AND data_type = 3 ) e ON a.marketId = e.market_id
LEFT JOIN ( SELECT market_id, branch_name, market_name, order_id AS orderCnt, order_total_money AS orderTotalMoney FROM dwd_old_for_new_all_detail WHERE old_goods_type = 1 AND data_type = 3 ) f ON a.marketId = f.market_id
WHERE
a.marketId IS NOT NULL
AND a.marketId IN (
'560807993181515776',
'561860259921571840'
)
GROUP BY
a.marketName, a.marketId, a.branchNam
原文地址: https://www.cveoy.top/t/topic/eHCj 著作权归作者所有。请勿转载和采集!