用条件合并mysql数据库表中的两行 [英] Merge two rows in mysql database table with condition

问题描述

我在 mysql 数据库中有表，在该表中我执行一些查询以显示表中的结果.但我想合并表格中的某些行.

I have table in mysql database and in that table I do some query to display result from the table. But I want to merge some row in my table.

这是我的表的结构和示例值.

This is the structure and example value of my table.

id | benefit | plan_day | price
-------------------------------
1 | Free Pick up | 100 | 540
2 | Free Tea | 100 | 540

该示例在 plan_day 中具有相同的值，但在 benefit 中具有不同的值.

That example have same value in plan_day but different value in benefit.

这是我的第二个例子，plan_day 和 benefit

This is my second example with different in plan_day and benefit

id | benefit | plan_day | price
-------------------------------
1 | Free Pick up | 110 | 540
2 | Free Tea | 100 | 540

我想知道的是，如果 plan_day 中的值相同，我想合并有条件的两行我只想合并 benefit 和 price 不是 SUM 但如果 plan_day 有不同的价值，我想合并 benefit 和那个 plan_day本身，我想SUM价格.

What I want to know is I want to merge that two rows with condition if the value in plan_day is the same I just want merge benefit and the price is not SUM but if plan_day have different value I want to merge benefit and that plan_day itself and I want to SUM the price.

这是我想显示的结果:

条件如果 plan_day 具有相同的值.

Condition if plan_day have same value.

Free Pick up, Free Tea | 100 | 540

条件如果 plan_day 具有不同的值.

Condition if plan_day have different value.

Free Pick up, Free Tea | 110, 100 | 1080

这就是我一直在做但没有成功的事情.

And this is what I have been do but not success.

SELECT GROUP_CONCAT(benefit SEPARATOR ',') AS benefit, GROUP_CONCAT(plan_day SEPARATOR ',') AS plan_day, SUM(price) as price
FROM special_offer

谁能帮我解决这个问题?谢谢.

Anyone can help me with this issue ? Thank you.

推荐答案

只需将 DISTINCT 子句添加到您的查询中:

Just add the DISTINCT clause to your query:

SELECT
  GROUP_CONCAT(DISTINCT benefit SEPARATOR ',') AS benefit,
  GROUP_CONCAT(DISTINCT plan_day SEPARATOR ',') AS plan_day,
  SUM(price) as price
FROM
  special_offer

这篇关于用条件合并mysql数据库表中的两行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！