为什么这个查询不能获取正确的记录？

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'
    ORDER by bs.step_number, p.price

它正在返回

    88  1   575.0000    Lenovo Thinkcentre POS PC
    92  1   799.0000    Lenovo Thinkcenter Server - RAID Configured
    31  1   1599.0000   All-In-One - Lenovo Thinkcentre 23"
    63  2   169.0000    Lenovo Thinkvision 18.5" - LCD
    62  2   249.0000    Lenovo Thinkvision 22" - LCD
    244 2   559.0000    Touchscreen with MSR - Firebox 15"
    104 3   285.0000    Remote Order Printer - Epson
    65  3   355.0000    Barcode and Label Printer - Zebra 2" TT
    68  3   399.0000    Barcode And Label Printer - Zebra 4" DT
    254 4   106.0000    Cash Drawer - APG - 14X16 - Black
    251 4   195.0000    Cash Drawer - APG - 16X16 - Serial
    97  4   395.0000    Aldelo Lite
    97  5   395.0000    Aldelo Lite
    121 5   549.0000    Cash Register Express  - Pro
    279 5   849.0000    Aldelo Premium
    135 6   0.0000      Free!! Payment Processing Software
    191 6   349.0000    Integrated Payment Processing
    231 7   0.0000    1 User/Location - 8Am - 8Pm Mon - Fri Support Plan - Level 1
    232 7   0.0000    1 User/Location - 24 X 7 X 365 Support Plan - Level 1
    155 7   369.0000    Accessory - Posiflex 12.1" LCD Customer Display

我需要的是每个步骤的最低价格，所以我假设添加子查询的工作方式如下

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'
      AND bp.builder_product_id = (
            SELECT builder_product_id
            FROM builder_product as alt
            WHERE alt.step_number = bp.step_number
            LIMIT 1
        )
    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

这是不正确的，因为如您所见，步骤#2应该返回

    63  2   169.0000    Lenovo Thinkvision 18.5" - LCD

由于169.000小于559.000，有什么办法改变这一点吗？

Answer 1

查看GROUP BY和MIN。尝尝这个

SELECT
  bp.product_id,bs.step_number,
  MIN(p.price) as 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 bp.product_id
ORDER by bs.step_number, p.price

Answer 2

您的subselect将返回返回的第一个条目，因为您使用的是LIMIT 1，但是，那里的结果是没有排序的。尝试按价格对子选择进行排序(这可能需要与products表连接以确定价格)。