MYSQL如何获取group by之前的第一行

发布于 2025-01-09 18:42:17 字数 1394 浏览 4 评论 0原文

我正在尝试获取按其组代码分组的产品，但按其售价排序。

当我运行此查询时：

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
LEFT JOIN product_category pc ON pc.product_id = p.id
LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

我得到以下结果：

当我添加分组依据并运行此查询时：

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
    LEFT JOIN product_category pc ON pc.product_id = p.id
    LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?)
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
GROUP BY p.group_code
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

我得到以下结果：

您可以看到，这并不是第一次查询时最便宜的产品。谁能帮我买到最便宜的结果？

原文

I am trying to fetch products grouped by it's group code, but sorted by it's selling price.

When I run this query:

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
LEFT JOIN product_category pc ON pc.product_id = p.id
LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

I am getting this result:

When I add a group by and run this query:

SELECT p.id, p.base_model, p.group_code, p.retail_price, p.selling_price, option_name
FROM product p
    LEFT JOIN product_category pc ON pc.product_id = p.id
    LEFT JOIN product_filter_value pfv1 ON p.id = pfv1.product_id
WHERE (pc.category_id = ?)
  AND (p.active = ?)
  AND (pfv1.filter_id = ?)
  AND (pfv1.filter_value_id IN (?))
  AND (p.type = "shop")
  AND (p.group_code = ?)
GROUP BY p.group_code
ORDER BY IF(p.combideal_active = 1, p.combideal_price, p.selling_price) asc

I am getting this result:

You can see, this is not the cheapest product from the first query. Can anyone help me out to getting the cheapest one as the result?

分享到QQ

分享到微博

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

发布评论

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

孤独难免 2025-01-16 18:42:17

这种操作在 MySQL 8.x 中很容易使用窗口函数来执行。但MySQL 5.7中没有这样的东西。

但是，您可以像这样使用group_concat：

select p.*
from   product p
inner join (
    select   p.group_code,
             cast(substring_index(group_concat(p.id 
                 order by if(p.combideal_active = 1, p.combideal_price, p.selling_price) 
                 separator ','
             ), ',', 1) as unsigned) id
    from     product p
    left join product_category pc ON pc.product_id = p.id
    left join product_filter_value pfv1 ON p.id = pfv1.product_id
    where /* ...etc... */
    group by p.group_code
) grouped on  p.id = grouped.id;

This kind of operations are easy to perform in MySQL 8.x, using window functions. But there is no such thing in MySQL 5.7.

You could however use group_concat like this:

select p.*
from   product p
inner join (
    select   p.group_code,
             cast(substring_index(group_concat(p.id 
                 order by if(p.combideal_active = 1, p.combideal_price, p.selling_price) 
                 separator ','
             ), ',', 1) as unsigned) id
    from     product p
    left join product_category pc ON pc.product_id = p.id
    left join product_filter_value pfv1 ON p.id = pfv1.product_id
    where /* ...etc... */
    group by p.group_code
) grouped on  p.id = grouped.id;

回复收藏 0 原文

~没有更多了~