列出组查询中的 id

发布于 2024-12-21 21:26:36 字数 548 浏览 3 评论 0原文

我想为每个具有唯一金额、操作、月份和费用的数据集群返回一行> 对于给定的 id。

表格如下：

在此处输入图像描述

我几乎可以得到我想要的东西

SELECT amount, operation, months, fee, plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

Which will get:

在此处输入图像描述

但请注意，当我希望它返回诸如 1 之类的内容时，它只返回一个 plan_id， 2, 3

是这可能吗？

原文

I want to return a row for each cluster of data that has a unique amount, operation, months, and fee for a given id.

Table is as follows:

enter image description here

I can almost get what I want with

SELECT amount, operation, months, fee, plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

Which will get:

enter image description here

But notice it only returns one plan_id when I want it to return something like 1, 2, 3

Is this possible?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

三生路 2024-12-28 21:26:36

您可以使用 GROUP_CONCAT

SELECT amount, operation, months, fee, 
    GROUP_CONCAT(DISTINCT plan_id SEPARATOR ',') AS plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

但请注意，默认最大长度为 1024，因此如果您使用大型表执行此操作，则可能会出现截断值...

You could use GROUP_CONCAT

SELECT amount, operation, months, fee, 
    GROUP_CONCAT(DISTINCT plan_id SEPARATOR ',') AS plan_id
FROM promo_discounts
WHERE promo_id = 1
GROUP BY amount, operation, months, fee

Beware though, that the default maximum length is 1024, so if you're doing this with a large table, you could have truncated values...

回复收藏 0 原文

请远离我 2024-12-28 21:26:36

看看 group_concat()< /a> 函数。

SELECT *, GROUP_CONCAT(id SEPARATOR ",") AS grouped_ids FROM 表

回复收藏 0 原文

帅气称霸 2024-12-28 21:26:36

如果您需要动态的东西，我不会推荐 GROUP_CONCAT。
GROUP_CONCAT 有限制。
您可以做的是使用临时表，您可以先选择其中的所有行，然后可以获得分组结果。
要获取 ID，您可以循环组并使用以所有组为条件的 WHERE 子句从临时表中进行选择。
我知道这对于这样的事情来说可能有点太复杂了，但我觉得如果你真的需要跟踪 ID，这是一个很好的解决方案。

回复收藏 0 原文

天冷不及心凉 2024-12-28 21:26:36

在处理这个问题时，我想到的是将 group by 结果与组字段上的同一个表连接起来。请注意，两个选择中的 WHERE 语句是相同的

SELECT plan_id
FROM promo_discounts pd 
JOIN (SELECT amount, operation, months, fee
    FROM promo_discounts 
    WHERE promo_id = 1 
    GROUP BY amount, operation, months, fee 
) AS grup ON pd.amount = grup.amount 
             AND pd.operation = grup.operation 
             AND pd.months = grup.months
             AND pd.fee = grup.fee
WHERE promo_id = 1

现在您可以使用结果来获得您想要的结果

What I came up with when dealing with this problem, is to join the group by result with the same table on the fields of the group. Notice that the WHERE statement is the same in both selects

SELECT plan_id
FROM promo_discounts pd 
JOIN (SELECT amount, operation, months, fee
    FROM promo_discounts 
    WHERE promo_id = 1 
    GROUP BY amount, operation, months, fee 
) AS grup ON pd.amount = grup.amount 
             AND pd.operation = grup.operation 
             AND pd.months = grup.months
             AND pd.fee = grup.fee
WHERE promo_id = 1

Now you can play with the result to get exactly what you want

回复收藏 0 原文

~没有更多了~