mysql - 连接 2 个表 - 结果根据需要不同

Question

所以我又来了。我有 2 个表:

ci_产品:

ci_prodimages:

http://img832.imageshack.us/img832/8672/kv3b.png

我有一个带有连接和 GROUP_CONCAT 的 SQL 查询。

SELECT *, GROUP_CONCAT(img_url) urls FROM (`ci_products`) 
JOIN `ci_prodimages` ON `ci_prodimages`.`img_pid`=`ci_products`.`prod_id` 
WHERE `prod_cat` = '4' OR `prod_cat` = '8' OR `prod_cat` = '9' OR `prod_cat` = '10'

此 SQL 应返回所有 3 个产品。但它只返回带有 prod_cat - 10 的那个。除了这个，其他一切都在工作。

还有这个查询:

SELECT *, GROUP_CONCAT(img_url) urls FROM (`ci_products`) 
JOIN `ci_prodimages` ON `ci_prodimages`.`img_pid`=`ci_products`.`prod_id` 
WHERE `prod_cat` = '9'

结果为空结果。

感谢任何帮助。谢谢

Answer 1

您正在使用 GROUP_CONCAT，这是一个聚合函数，但没有使用 GROUP BY。 MySQL 确实允许隐式分组，您可以在其中选择既不在分组依据也不在聚合中的列，但如您所见，您可能会遇到问题。我也不推荐使用 SELECT *，所以你的查询最好写成:

SELECT  p.prod_id, 
        p.Prod_Name, 
        p.prod_desc, 
        p.prod_nem, 
        p.prod_cat, 
        p.prod_minpric,
        p.prd_minbid,
        p.prod_marks,
        p.prod_added, GROUP_CONCAT(img_url) urls 
FROM    `ci_products` p
        JOIN `ci_prodimages` i
            ON p.`img_pid`= i.`prod_id` 
WHERE   `prod_cat` = '9'
GROUP BY p.prod_id, p.Prod_Name, p.prod_desc, p.prod_nem, p.prod_cat, p.prod_minpric, p.prd_minbid, p.prod_marks, p.prod_added

在 MySQL 中(并且根据 SQL 标准)以下也是合法的:

SELECT  p.prod_id, 
        p.Prod_Name, 
        p.prod_desc, 
        p.prod_nem, 
        p.prod_cat, 
        p.prod_minpric,
        p.prd_minbid,
        p.prod_marks,
        p.prod_added, GROUP_CONCAT(img_url) urls 
FROM    `ci_products` p
        JOIN `ci_prodimages` i
            ON p.`img_pid`= i.`prod_id` 
WHERE   `prod_cat` = '9'
GROUP BY p.prod_id;

因为 prod_id 是表 ci_products 的主键，所以没有必要按所有列分组，因为它们在功能上依赖于主键，但是，MySQL 确实没有严格执行此标准，这可能会导致意外行为，因此我仍然建议明确列出您分组所依据的所有列。

下面是我以前写的标准解释，用于解释 MySQL 隐式分组的工作原理，以及我为什么避免它:

---

我建议尽可能避免 MySQL 提供的隐式分组，我的意思是在选择列表中包括列，即使它们不包含在聚合函数或 group by 子句中。

想象一下下面的简单表格 (T):

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |
2   |    A    |    Y     |

在MySQL中你可以写

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1;

这实际上违反了 SQL 标准，但它在 MySQL 中有效，但问题是它是不确定的，结果:

ID  | Column1 | Column2  |
----|---------+----------|
1   |    A    |    X     |

不比

更正确或更不正确

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

所以你的意思是为 Column1 的每个不同值提供一行，两个结果集都满足，那么你怎么知道你会得到哪一个？嗯，你没有，这似乎是一个相当普遍的误解，你可以添加和 ORDER BY 子句来影响结果，例如下面的查询:

SELECT  ID, Column1, Column2
FROM    T
GROUP BY Column1
ORDER BY ID DESC;

将确保您获得以下结果:

ID  | Column1 | Column2  |  
----|---------+----------|
2   |    A    |    Y     |

因为 ORDER BY ID DESC，但这不是真的 (as demonstrated here)。

MySQL documents状态:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

因此，即使您有订单，但在每组选择一行之后才会应用，并且这一行是不确定的。

SQL 标准确实允许选择列表中的列不包含在 GROUP BY 或聚合函数中，但是这些列必须在功能上依赖于 GROUP BY 中的列。例如，示例表中的 ID 是 PRIMARY KEY，所以我们知道它在表中是唯一的，所以下面的查询符合 SQL 标准，在 MySQL 中运行，在目前许多 DBMS 中失败(在编写 Postgresql 时是我所知道的最接近正确实现标准的 DBMS):

SELECT  ID, Column1, Column2
FROM    T
GROUP BY ID;

由于每一行的 ID 都是唯一的，因此每个 ID 只能有一个 Column1 值，一个 Column2 值，对于返回什么没有歧义每行。