问题描述
我很难将一段 MySQL 代码翻译成 Access.我正在尝试将 Sakila (MySQL) 数据库中的查询之一用于我正在处理的 Access 项目.
I'm having a really hard time translating a piece of MySQL-code to Access. I'm trying to use one of the queries found in the Sakila (MySQL) Database for an Access project I'm working on.
首先,GROUP_CONCAT 函数根本不起作用.经过一些谷歌搜索后,我发现 Access 不支持此功能,但我找不到可行的替代方案.然而,CONCAT 可以被一些+"操作符代替.
First of all, the GROUP_CONCAT function doesn't work at all. After some Google searches I found out that Access doesn't support this function but I couldn't find a working alternative. CONCAT however could be replaced by a few '+' operators.
然后是三重 LEFT JOIN,它不断返回一个缺少操作符的错误.我找到了一篇博客文章,解释了一系列括号如何提供帮助,但这导致了更多的麻烦,并促使我删除括号,之后它会引发更多丢失的操作员错误.
Then comes the triple LEFT JOIN which kept returning a missing operator error. I found a blog post explaining how a series of brackets could help, but this resulted in even more trouble and prompted me to remove the brackets after which it threw more missing operator errors.
此外,SEPARATOR 似乎也不被接受,但这可能是由于 GROUP_CONCAT 不起作用.
Also, SEPARATOR doesn't seem to be accepted as well, but this could be due to GROUP_CONCAT not functioning.
有没有人愿意让我朝着正确的方向前进?我已经为此苦苦挣扎了太久.
Is there anyone willing to get me in the right direction? I've been struggling with this for way too long.
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ',
(SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ')
FROM film f
INNER JOIN film_category fc
ON f.film_id = fc.film_id
INNER JOIN film_actor fa
ON f.film_id = fa.film_id
WHERE fc.category_id = c.category_id
AND fa.actor_id = a.actor_id
)
)
ORDER BY c.name SEPARATOR '; ')
AS film_info
FROM
actor AS a
LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
LEFT JOIN film_category AS fc ON fa.film_id = fc.film_id
LEFT JOIN category AS c ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name
推荐答案
最常被引用的 MySQL GROUP_CONCAT()
函数的 Access 替代方案是 Allen Browne 的 ConcatRelated()
> 功能,在此处可用.
The most commonly-cited Access alternative to the MySQL GROUP_CONCAT()
function is Allen Browne's ConcatRelated()
function, available here.
至于 JOIN 周围的括号,是的,Access SQL 对这些很挑剔.而不是
As for parentheses around JOINs, yes, Access SQL is fussy about those. Instead of
FROM
actor AS a
LEFT JOIN film_actor AS fa ON a.actor_id = fa.actor_id
LEFT JOIN film_category AS fc ON fa.film_id = fc.film_id
LEFT JOIN category AS c ON fc.category_id = c.category_id
试试
FROM
(
(
actor AS a
LEFT JOIN
film_actor AS fa
ON a.actor_id = fa.actor_id
)
LEFT JOIN
film_category AS fc
ON fa.film_id = fc.film_id
)
LEFT JOIN
category AS c
ON fc.category_id = c.category_id
这篇关于将 MySQL 代码转换为 Access:GROUP_CONCAT 和三重 JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!