select sum(NOW_OP.UV), sum(PRE_OP.UV) from v_shop left JOIN ( SELECT DISTINCT SHOP_ID, STATISTIC_DATE, UV AS UV, Depth_of_visit AS VISIT, Payment AS PAYMENT, Sales_Value AS GMV, Refund AS REFUND, CVR AS CVR, Bounce_Rate AS BOUNCE, s.item_score AS EXPLAIN_EVALUATE, s.service_score AS SERVICE_EVALUATE, s.delivery_score AS LOGISTICS_EVALUATE, KPI AS KPI FROM TCC_OPERATE o JOIN tcc_item i ON o.SHOP_ID = i.ITEM_ID RIGHT JOIN tcc_shops s ON i.ITEM_NAME = s.nick ) AS PRE_OP ON PRE_OP.SHOP_ID = V_SHOP.SEL_ITEM_ID and DATE_FORMAT(PRE_OP.STATISTIC_DATE, '%Y%m%d') BETWEEN 20161121 AND 20161122 LEFT JOIN ( SELECT DISTINCT SHOP_ID, STATISTIC_DATE, UV AS UV, Depth_of_visit AS VISIT, Payment AS PAYMENT, Sales_Value AS GMV, Refund AS REFUND, CVR AS CVR, Bounce_Rate AS BOUNCE, s.item_score AS EXPLAIN_EVALUATE, s.service_score AS SERVICE_EVALUATE, s.delivery_score AS LOGISTICS_EVALUATE, KPI AS KPI FROM TCC_OPERATE o join tcc_item i on o.SHOP_ID = i.ITEM_ID RIGHT JOIN tcc_shops s on i.ITEM_NAME = s.nick ) AS NOW_OP ON NOW_OP.SHOP_ID = V_SHOP.SEL_ITEM_ID and DATE_FORMAT(NOW_OP.STATISTIC_DATE,'%Y%m%d') BETWEEN 20161123 AND 20161124 where v_shop.SEL_ITEM_ID = 3
结果是:
2000 1000
但真实数据是:
1000 500
这个sql该怎么改呢?
v_shop.SEL_ITEM_ID = 3 只有1条数据
select a. * from (select sum(NOW_OP.UV), sum(PRE_OP.UV) from v_shop where v_shop.SEL_ITEM_ID = 3) a
left join.....
..
--where v_shop.SEL_ITEM_ID = 3
select sum(NOW_OP.UV), sum(PRE_OP.UV) from v_shop where v_shop.SEL_ITEM_ID = 3
这个是错的,v_shop查不出来uv