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不是这样工作的。如果按多个列进行分组，则select只能返回：

从其他列(如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允许非常宽松的语法，并且会很乐意地删除每个组中的随机行，但其他数据库管理系统会在原始查询中触发错误。)

Answer 2

连接到仅包含每个组的最小值的表的子选择

在本例中。mygroup min(amt)返回组的最低金额项

然后，我将此连接回主表，作为一个完整的内连接，以将记录限制为最小记录。

Select A.myGROUP, A.Amt
from mtest A
  INNER JOIN (Select myGroup, min(Amt) as minAmt from mtest group by mygroup) B
  ON B.myGroup=A.mygroup
  and B.MinAmt = A.Amt

Answer 3

是。每个不同的组密钥只返回一次。这个问题不容易解决。运行两个不同的查询，然后组合结果。如果这不是一个选项，则为每个步骤的最低价格创建一个临时表，将这些表连接到查询中。