mysql 组没有做我期望的事情？

Question

SELECT
      bp.product_id,bs.step_number,
      p.price, pd.name as product_name
    FROM 
      builder_product bp
      JOIN builder_step bs ON bp.builder_step_id = bs.builder_step_id
      JOIN builder b ON bp.builder_id = b.builder_id
      JOIN product p ON p.product_id = bp.product_id
      JOIN product_description pd ON p.product_id = pd.product_id
      WHERE b.builder_id = '74' and bs.optional != '1'
    group by bs.step_number
    ORDER by bs.step_number, p.price

但这是我的结果

88  1   575.0000    Lenovo Thinkcentre POS PC
244 2   559.0000    Touchscreen with MSR - Firebox 15"
104 3   285.0000    Remote Order Printer - Epson
97  4   395.0000    Aldelo Lite
121 5   549.0000    Cash Register Express  - Pro
191 6   349.0000    Integrated Payment Processing
155 7   369.0000    Accessory - Posiflex 12.1" LCD Customer Display

Answer 1

这不是 GROUP BY 应该如何工作的。如果您按多列分组，您的选择只能返回:

• 分组依据的列
• 其他列的聚合函数，例如 MIN()、MAX()、AVG()...

所以你需要这样做:

SELECT
  bs.step_number,
  MIN(p.price) AS min_price, pd.name as product_name
FROM 
  builder_product bp
  JOIN builder_step bs ON bp.builder_step_id = bs.builder_step_id
  JOIN builder b ON bp.builder_id = b.builder_id
  JOIN product p ON p.product_id = bp.product_id
  JOIN product_description pd ON p.product_id = pd.product_id
  WHERE b.builder_id = '74' and bs.optional != '1'
group by bs.step_number, pd.name
ORDER by bs.step_number, min_price

(MySQL 允许非常宽松的语法，并且会很乐意删除每个组的随机行，但其他 DBMS 会触发您的原始查询错误。)