mysql - 通过类别返回属于某个部门的所有产品的 SQL 过程

Question

至少我认为它正在做的事情。我不确定如何使用正确的参数调用过程。

这是 SQL 过程:

CREATE PROCEDURE catalog_get_products_on_department(
  IN inDepartmentId INT, IN inShortProductDescriptionLength INT,
  IN inProductsPerPage INT, IN inStartItem INT)
BEGIN
  PREPARE statement FROM
    "SELECT DISTINCT p.product_id, p.name,
                     IF(LENGTH(p.description) <= ?,
                        p.description,
                        CONCAT(LEFT(p.description, ?),
                               '...')) AS description,
                     p.price, p.discounted_price, p.thumbnail
     FROM            product p
     INNER JOIN      product_category pc
                       ON p.product_id = pc.product_id
     INNER JOIN      category c
                       ON pc.category_id = c.category_id
     WHERE           (p.display = 2 OR p.display = 3)
                     AND c.department_id = ?
     ORDER BY        p.display DESC
     LIMIT           ?, ?";

  SET @p1 = inShortProductDescriptionLength;
  SET @p2 = inShortProductDescriptionLength;
  SET @p3 = inDepartmentId;
  SET @p4 = inStartItem;
  SET @p5 = inProductsPerPage;

  EXECUTE statement USING @p1, @p2, @p3, @p4, @p5;
END$$

这是架构的片段:

这就是当我尝试使用以下参数调用该过程时发生的情况:

inDepartmentId: 1,
inShortProductDescriptionLength: 10,
inProductsPerPage: 10,
nStartItem: 1

The following query has failed: "SET @p0='1'; SET @p1='10'; SET @p2='10'; SET @p3='1'; CALL `catalog_get_products_on_department`(@p0, @p1, @p2, @p3); "

MySQL said: #3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'turing.p.display' which is not in SELECT list; this is incompatible with DISTINCT

Answer 1

错误消息:

MySQL said: #3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'turing.p.display' which is not in SELECT list; this is incompatible with DISTINCT

告诉它需要:

a.将列 turing.p.display 添加到列列表

这样语句就变成:

SELECT DISTINCT p.product_id, p.name,
                 IF(LENGTH(p.description) <= ?,
                    p.description,
                    CONCAT(LEFT(p.description, ?),
                           '...')) AS description,
                 p.price, p.discounted_price, p.thumbnail,
                 p.display 
 FROM            product p
 INNER JOIN      product_category pc
                   ON p.product_id = pc.product_id
 INNER JOIN      category c
                   ON pc.category_id = c.category_id
 WHERE           (p.display = 2 OR p.display = 3)
                 AND c.department_id = ?
 ORDER BY        p.display DESC
 LIMIT           ?, ?";

(注意添加了新列p.display

或

b.删除DISTINCT

这必须消除错误消息，但可能会在输出中添加重复项。

c.完全删除 ORDER BY

这当然消除了错误的原因，但数据的顺序可能会改变甚至变得随机(取决于表/索引的物理组织、版本、月相等)

我认为“a”总体来说是最好的。