使用两个左连接mysql时的重复值 [英] repetitive values when using two left join mysql

问题描述

在正负字段中，值相同.请帮助解决这个问题...

in positive and negative field, values are same. please help to fix this problem...

   SELECT 
    name,
    COUNT(p.service_provider_id) as positive,
    COUNT(n.service_provider_id) as negative


 FROM tablesite

    LEFT JOIN action p
    ON tablesite.id_user=p.service_provider_id
    AND p.vote !='' AND p.customer_comment ='' //equal

    LEFT JOIN action n
    ON tablesite.id_user=n.service_provider_id
    AND n.vote !='' AND n.customer_comment !='' //unequal
    GROUP BY name

推荐答案

说明目的

-- drop table t1;
create table t1
(   id int auto_increment primary key,
    theName varchar(10) not null,
    birthDate date not null
);

-- drop table t2;
create table t2
(   id int auto_increment primary key,
    refId int not null,
    comments varchar(1000) not null, -- your choice, but this is my demo :)
    key(refId)
);

insert t1 (theName,birthDate) values ('cat','2014-11-01'),('mouse','2014-12-21');

insert t2(refId,comments) values
(1,"i like this cat"),(1,"he is fury"),(1,""),
(2,"more cheese");

查询

select t1.id,t1.theName,t1.birthDate,
SUM(CASE WHEN t2.comments!="" THEN 1 ELSE 0 END) commentCount,
SUM(CASE WHEN t2.comments="" THEN 1 ELSE 0 END) notAcommentCount
from t1
join t2
on t2.refId=t1.id
group by t1.id,t1.theName,t1.birthDate;

+----+---------+------------+--------------+------------------+
| id | theName | birthDate  | commentCount | notAcommentCount |
+----+---------+------------+--------------+------------------+
|  1 | cat     | 2014-11-01 |            2 |                1 |
|  2 | mouse   | 2014-12-21 |            1 |                0 |
+----+---------+------------+--------------+------------------+

这就是你想要的吗?

答案:

SELECT 
tablesite.name,
SUM(CASE WHEN action.vote !="" AND action.customer_comment ="" THEN 1 ELSE 0 END) مثبت,
SUM(CASE WHEN action.vote !="" AND action.customer_comment !="" THEN 1 ELSE 0 END) منفی

FROM tablesite

JOIN action
ON tablesite.id_user=action.service_provider_id
group by tablesite.name 

这篇关于使用两个左连接mysql时的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！