MySQL语句(FULL JOIN上的语法错误) [英] Mysql statement (syntax error on FULL JOIN)

问题描述

我的sql语句出了什么问题，它说问题在FULL JOIN附近，但是我很困惑:

What is wrong with my sql statement, it says that the problem is near the FULL JOIN, but I'm stumped:

SELECT `o`.`name` AS `offername`, `m`.`name` AS `merchantName` 
FROM `offer` AS `o` 
FULL JOIN `offerorder` AS `of` ON of.offerId = o.id 
INNER JOIN `merchant` AS `m` ON o.merchantId = m.id 
GROUP BY `of`.`merchantId` 

请谨慎，因为我不是sql基金会

Please be gentle, as I am not a sql fundi

推荐答案

MySQL不提供完全连接，您可以使用

MySQL doesn't offer full join, you can either use

• 一对LEFT + RIGHT和UNION；或
• 使用LEFT，RIGHT和INNER和UNION ALL的三元组

查询也是非常错误的，因为您有GROUP BY，但是SELECT列不是聚合的.

The query is also very wrong, because you have a GROUP BY but your SELECT columns are not aggregates.

将其正确转换为LEFT + RIGHT + UNION后，仍然存在从每个不同的of.merchantid的任何随机记录中获取要约名称和商人名称的问题，甚至不一定从同一记录中获取.

After you convert this properly to LEFT + RIGHT + UNION, you still have the issue of getting an offername and merchantname from any random record per each distinct of.merchantid, and not even necessarily from the same record.

因为您对o.merchant拥有INNER JOIN条件，所以不需要进行FULL JOIN，因为"offerorder"中与"offer"不匹配的记录将使INNER JOIN失败.它将变成左联接(可选).因为您要在of.merchantid上分组，所以所有缺少的offerorder记录都将在"NULL"下分组为商人ID.

Because you have an INNER JOIN condition against o.merchant, the FULL JOIN is not necessary since "offerorder" records with no match in "offer" will fail the INNER JOIN. That turns it into a LEFT JOIN (optional). Because you are grouping on of.merchantid, any missing offerorder records will be grouped together under "NULL" as merchantid.

此查询将有效，对于每个商人ID，将仅显示商人提出的一个要约(按字典顺序排序时带有名字的一个要约).

This is a query that will work, for each merchantid, it will show just one offer that the merchant made (the one with the first name when sorted in lexicographical order).

SELECT MIN(o.name) AS offername, m.name AS merchantName 
FROM offer AS o 
LEFT JOIN offerorder AS `of` ON `of`.offerId = o.id 
INNER JOIN merchant AS m ON o.merchantId = m.id 
GROUP BY `of`.merchantId, m.name

注意:高度怀疑连接o.merchantid = m.id.您是说of.merchantid = m.id吗?如果是这种情况，请将LEFT更改为RIGHT联接.

Note: The join o.merchantid = m.id is highly suspect. Did you mean of.merchantid = m.id? If that is the case, change the LEFT to RIGHT join.

这篇关于MySQL语句(FULL JOIN上的语法错误)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！