首页新闻找找看学习计划

MySql sql 语句怎么查询出不重复的行数,不是distinct.

0
[已解决问题] 解决于 2016-12-18 14:49
复制代码
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条数据

Eysa的主页 Eysa | 初学一级 | 园豆:68
提问于:2016-11-24 14:03
< >
分享
最佳答案
0

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

奖励园豆:5
Daniel Cai | 专家六级 |园豆:10374 | 2016-11-24 16:36

select sum(NOW_OP.UV), sum(PRE_OP.UV) from v_shop where v_shop.SEL_ITEM_ID = 3

 

这个是错的,v_shop查不出来uv

Eysa | 园豆:68 (初学一级) | 2016-11-24 16:40
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册