将MySQL代码转换为Access:GROUP_CONCAT和三重JOIN [英] Converting MySQL code to Access: GROUP_CONCAT and a triple JOIN

问题描述

我很难将一段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函数根本不起作用.经过一些Google搜索后，我发现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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！