MySQL:一起使用 ORDER BY 和 GROUP BY

Question

我最近升级到 MySQL 5.7.22，我的查询停止工作。我有两个表“items”和“packages”，我试图在其中为每个项目输出一行，包括一列具有最低单位价格的包裹，但忽略单位价格设置为 0 的包裹。

这是表格和数据的最小示例:

CREATE TABLE `items` (
  `id` int(11) NOT NULL
);


CREATE TABLE `packages` (
  `item_id` int(11) NOT NULL,
  `price_per_unit` float(16,6) DEFAULT 0
);


INSERT INTO `items` (`id`) VALUES
(1),
(2),
(3);


INSERT INTO `packages` (`item_id`, `price_per_unit`) VALUES
(1, 0.45),
(1, 0),
(1, 0.56),
(1, 0.34);

这是查询:

SELECT
  *
FROM
  (
  SELECT
    items.id,
    NULLIF(pkgs.ppu, 0) AS mppu
  FROM
    items
  LEFT JOIN
  (
    SELECT
      item_id,
      price_per_unit AS ppu
    FROM
      packages
  ) AS pkgs ON pkgs.item_id = items.id
  ORDER BY
    IFNULL(mppu, 9999)
  ) X
GROUP BY
  X.id

我将零值设置为 null，然后在排序过程中将它们的值提高得更高。必须有更好的方法(特别是因为此方法不再有效)。

此数据的预期输出是:

id    mppu
1     0.34
2     null
3     null

Answer 1

我认为您的查询有点太复杂了。这个呢？

SELECT i.id,IFNULL(Min(p.price_per_unit), 'NONE')
FROM   items i
       LEFT JOIN packages p
              ON ( i.id = p.item_id )
WHERE  p.price_per_unit > 0
        OR p.price_per_unit IS NULL
GROUP  BY i.id

查看此 fiddle .我使用了这些数据:

INSERT INTO `items` (`id`) VALUES
(1),(2),(3);


INSERT INTO `packages` (`item_id`, `price_per_unit`) VALUES
(1, 0.45),
(1, 0),
(1, 0.56),
(1, 0.34),
(2, 9.45),
(2, 0),
(2, 0.56),
(2, 0.14);

得到这个结果:

id  IFNULL(min(p.price_per_unit),'None')
1   0.340000
2   0.140000
3   None