我在MySQL
数据库中有两个表:
表格1
mysql> SELECT ID FROM table1;
+----+
| id |
+----+
| 6 |
+----+
1 row in set (0.00 sec)
表2
mysql> SELECT * FROM table2;
+----+----------+------------------------+----------------------+
| id | placeId | dictionaryId | dictionaryCode |
+----+----------+------------------------+----------------------+
| 54 | 6 | 1 | MarketingTerritoryID |
| 53 | 6 | 1 | PlaceTypeID |
+----+----------+------------------------+----------------------+
2 rows in set (0.00 sec)
您能否解释一下以下sql查询为何起作用:
SELECT
table1.id
FROM table1
LEFT JOIN table2 AS placeType
ON table1.id = placeType.placeId
AND placeType.dictionaryCode = 'PlaceTypeID'
LEFT JOIN table2 AS region
ON table1.id = region.placeId
AND region.dictionaryCode = 'MarketingTerritoryID'
GROUP BY region.dictionaryId
HAVING region.dictionaryId = MIN(1)
...和另一个
SELECT
table1.id
FROM table1
LEFT JOIN table2 AS placeType
ON table1.id = placeType.placeId
AND placeType.dictionaryCode = 'PlaceTypeID'
LEFT JOIN table2 AS region
ON table1.id = region.placeId
AND region.dictionaryCode = 'MarketingTerritoryID'
GROUP BY region.dictionaryId, placeType.dictionaryId
HAVING region.dictionaryId = MIN(1)
返回sql错误: *Unknown column 'region.dictionaryId' in 'having clause'*
请注意,这两个查询之间的区别仅在于group子句:第二个查询也按以下方式分组 placeType.dictionaryId
这是来自sqlfiddle的代码:
我已将错误报告给mysql团队。这是链接:https : //bugs.mysql.com/bug.php?id=82317
目前它已验证状态。让我们看看他们会回答什么。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句