• <i id='49d17'><tr id='49d17'><dt id='49d17'><q id='49d17'><span id='49d17'><b id='49d17'><form id='49d17'><ins id='49d17'></ins><ul id='49d17'></ul><sub id='49d17'></sub></form><legend id='49d17'></legend><bdo id='49d17'><pre id='49d17'><center id='49d17'></center></pre></bdo></b><th id='49d17'></th></span></q></dt></tr></i><div id='49d17'><tfoot id='49d17'></tfoot><dl id='49d17'><fieldset id='49d17'></fieldset></dl></div>

      <small id='49d17'></small><noframes id='49d17'>

    1. <legend id='49d17'><style id='49d17'><dir id='49d17'><q id='49d17'></q></dir></style></legend>

        • <bdo id='49d17'></bdo><ul id='49d17'></ul>
        <tfoot id='49d17'></tfoot>

        将 MySQL 代码转换为 Access:GROUP_CONCAT 和三重 JOIN

        时间:2023-10-09
      1. <legend id='DgSVk'><style id='DgSVk'><dir id='DgSVk'><q id='DgSVk'></q></dir></style></legend><tfoot id='DgSVk'></tfoot>

              <bdo id='DgSVk'></bdo><ul id='DgSVk'></ul>
              <i id='DgSVk'><tr id='DgSVk'><dt id='DgSVk'><q id='DgSVk'><span id='DgSVk'><b id='DgSVk'><form id='DgSVk'><ins id='DgSVk'></ins><ul id='DgSVk'></ul><sub id='DgSVk'></sub></form><legend id='DgSVk'></legend><bdo id='DgSVk'><pre id='DgSVk'><center id='DgSVk'></center></pre></bdo></b><th id='DgSVk'></th></span></q></dt></tr></i><div id='DgSVk'><tfoot id='DgSVk'></tfoot><dl id='DgSVk'><fieldset id='DgSVk'></fieldset></dl></div>

              <small id='DgSVk'></small><noframes id='DgSVk'>

                    <tbody id='DgSVk'></tbody>
                • 本文介绍了将 MySQL 代码转换为 Access:GROUP_CONCAT 和三重 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 函数根本不起作用.经过一些谷歌搜索后,我发现 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:MS Access 调用 SQL Server 存储过程 下一篇:如何以编程方式创建 DSN?

                  相关文章

                  <legend id='WQxIy'><style id='WQxIy'><dir id='WQxIy'><q id='WQxIy'></q></dir></style></legend>

                • <tfoot id='WQxIy'></tfoot>
                    <bdo id='WQxIy'></bdo><ul id='WQxIy'></ul>
                • <small id='WQxIy'></small><noframes id='WQxIy'>

                  <i id='WQxIy'><tr id='WQxIy'><dt id='WQxIy'><q id='WQxIy'><span id='WQxIy'><b id='WQxIy'><form id='WQxIy'><ins id='WQxIy'></ins><ul id='WQxIy'></ul><sub id='WQxIy'></sub></form><legend id='WQxIy'></legend><bdo id='WQxIy'><pre id='WQxIy'><center id='WQxIy'></center></pre></bdo></b><th id='WQxIy'></th></span></q></dt></tr></i><div id='WQxIy'><tfoot id='WQxIy'></tfoot><dl id='WQxIy'><fieldset id='WQxIy'></fieldset></dl></div>