当前位置: 动力学知识库 > 问答 > 编程问答 >

php - How to remove duplicate values from an sql query

问题描述:

I want to know how to remove duplicate values from the output of an sql query.

This is the sql query:

$query = "SELECT useraccount.Username, tariff.Name as tariffs,

energyconsumption.ElecEnergy, useraccount.Username as User

FROM useraccount

INNER JOIN tariff

ON useraccount.tariffs = tariff.id

INNER JOIN energyconsumption

ON energyconsumption.User = useraccount.id

WHERE Date = CURRENT_DATE";

This is the output of that query:

{"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"2000"},

{"Username":"absc868","TariffName":"s1","ElecConsump":"1900"}]}

As you can see, the query filters out data where the data matches todays date. We have 2 outputs for the same user. The value of the tariff name and username are the same,but the energy consumption value is different which is fine.

I want to achieve the following output:

= {"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"2000 +1900"}

= {"results":[{"Username":"absc868","TariffName":"s1","ElecConsump":"3900"}

Could someone point me to the direction in how I can achieve this?

Thank you in advance to those who read the post and contributed!

网友答案:

You should use a sum and a group by

$query = "SELECT useraccount.Username as Username, tariff.Name as TariffName, 
sum(energyconsumption.ElecEnergy) as ElecConsump
    FROM useraccount
    INNER JOIN tariff 
ON useraccount.tariffs = tariff.id
INNER JOIN energyconsumption
ON energyconsumption.User = useraccount.id
WHERE Date = CURRENT_DATE
GROUP BY useraccount.Username, tariff.Name as tariffs";

(you have some difference between table column name alias and object attribute name )

网友答案:

You can use the group_concat function:

$query = "SELECT useraccount.Username, tariff.Name as tariffs, 
GROUP_CONCAT(energyconsumption.ElecEnergy SEPARATOR ' +')
    FROM useraccount
    INNER JOIN tariff 
ON useraccount.tariffs = tariff.id
INNER JOIN energyconsumption
ON energyconsumption.User = useraccount.id
WHERE Date = CURRENT_DATE
GROUP BY useraccount.Username, tariff.Name";
分享给朋友:
您可能感兴趣的文章:
随机阅读: