我在mysql数据库中有表,在该表中我执行了一些查询以显示表中的结果。但是我想合并表中的某行。
这是我表的结构和示例值。
id | benefit | plan_day | price
-------------------------------
1 | Free Pick up | 100 | 540
2 | Free Tea | 100 | 540
该示例在plan_day上具有相同的价值,但在收益上具有不同的价值。
这是我的第二个例子,plan_day和好处不同
id | benefit | plan_day | price
-------------------------------
1 | Free Pick up | 110 | 540
2 | Free Tea | 100 | 540
我想知道的是,如果plan_day中的值相同,我想合并带有条件的两行,我只想合并收益并且价格不SUM
但如果plan_day具有不同的价值,我想合并收益,这plan_day本身,我想SUM
价格。
这是我想要显示的结果:
条件plan_day是否具有相同的值。
Free Pick up, Free Tea | 100 | 540
条件plan_day是否具有不同的值。
Free Pick up, Free Tea | 110, 100 | 1080
这就是我一直在做的事情,但不是成功。
SELECT GROUP_CONCAT(benefit SEPARATOR ',') AS benefit, GROUP_CONCAT(plan_day SEPARATOR ',') AS plan_day, SUM(price) as price
FROM special_offer
任何人都可以帮助我解决这个问题吗?谢谢。
只需将DISTINCT
子句添加到查询中:
SELECT
GROUP_CONCAT(DISTINCT benefit SEPARATOR ',') AS benefit,
GROUP_CONCAT(DISTINCT plan_day SEPARATOR ',') AS plan_day,
SUM(price) as price
FROM
special_offer
我想你想要这个:
select plan_day,
group_concat(benefit order by id separator ',') benefits,
max(price) price
from t
group by plan_day
having count(*) > 1
union all
select *
from (
select group_concat(t1.plan_day order by t1.id separator ',') plan_day,
group_concat(t1.benefit order by t1.id separator ','),
sum(t1.price) price
from t t1
join (
select plan_day
from t
group by plan_day
having count(*) = 1
) t2 on t1.plan_day = t2.plan_day
)
where plan_day is not null;
对于此数据:
(1, 'Free Pick up', 110, 540 ),
(2, 'Free Tea', 100, 540 );
生产:
110,100 Free Pick up,Free Tea 1080
对于数据
(1, 'Free Pick up', 100, 540 ),
(2, 'Free Tea', 100, 540 );
生产:
100 Free Pick up,Free Tea 540