mysql - 仅当 GROUP BY 上不存在 A 行时如何选择 B 行

Question

我正在经历以下情况，并没有找到解决这个问题的好方法。我正在对 API 进行优化，因此正在寻找最快的解决方案。

下面的描述并不完全是我在做的，但我认为它很好地代表了问题。

假设我有一张产品表:

+----+----------+
| id |   name   |
+----+----------+
|  1 | product1 |
|  2 | product2 |
+----+----------+

我有一个每个产品的附件表，按语言分开:

+----+----------+------------+-----------------------+
| id | language | product_id |     attachment_url    |
+----+----------+------------+-----------------------+
|  1 |    bb    |      1     |     image1_bb.jpg     |
|  1 |    en    |      1     |     image1_en.jpg     |
|  1 |    pt    |      1     |     image1_pt.jpg     |
|  2 |    bb    |      1     |     image2_bb.jpg     |
|  2 |    pt    |      1     |     image2_pt.jpg     |
+----+----------+------------+-----------------------+

我打算做的是根据请求中选择的语言获取正确的附件。正如您在上面看到的，我可以为每个产品添加多个附件。我们使用 Babel (bb) 作为通用语言，所以每次我没有正确语言的附件时，我应该得到 babel 版本。同样重要的是要考虑附件表的主键是 id + language 的组合。

因此，假设我尝试获取 pt 中的所有数据，我创建 SQL 查询的第一个选择是:

SELECT p.id, p.name, 
    GROUP_CONCAT( '{',a.id,',',a.attachment_url, '}' ) as attachments_list 
FROM products p 
LEFT JOIN attachments a 
    ON (a.product_id=p.id AND (a.language='pt' OR a.language='bb')) 

问题是，通过这个查询，我总是得到 bb 数据，而且我只想在没有正确语言的附件时得到它。

我已经尝试执行子查询更改附件:

(SELECT * FROM attachments GROUP BY id ORDER BY id ASC, language DESC)

但它会加倍请求的时间。

我还尝试在 GROUP_CONCAT 中使用 DISTINCT，但它仅在每行的整体结果相等时才有效，所以它对我不起作用。

有人知道我可以直接应用于查询的任何其他解决方案吗？

编辑:

结合@Vulcronos 和@Barmar 的答案，最终解决方案比我最初建议的解决方案至少快 2 倍。

只是为正在寻找它的其他人添加一些上下文。我正在使用 Phalcon。因此，我在将各个部分组合在一起时遇到了很多麻烦，因为 Phalcon PHQL 不支持子查询，也不支持我必须使用的许多其他东西。

对于我的场景，我必须交付大约 1.2MB 的 JSON 内容，其中包含超过 2100 个对象，使用自定义查询使总请求时间比 Phalcon 本地关系管理方法快 3 倍(hasMany()、hasManyToMany() 等)并且比我原来的解决方案(使用了很多 find() 方法)快 10 倍).

Answer 1

尝试做两个连接而不是一个连接:

SELECT p.id, p.name, 
    GROUP_CONCAT( '{',COALESCE(a.id, b.id),',',COALESCE(a.attachment_url, b.attachment_url), '}' ) as attachments_list 
FROM products p 
LEFT JOIN attachments a 
    ON (a.product_id=p.id AND a.language='pt') 
LEFT JOIN attachments b
    ON (a.product_id=p.id AND a.language='bb') 

然后如果 a 不存在，则使用 COALESCE 返回 b 而不是 a。如果上述方法不起作用，您也可以使用子选择来完成。