LISTAGG中的LISTAGG

Question

我使用一些在线转换器将我在MySQL中的视图转换为oracle，结果是：

CREATE  VIEW actor_info
AS
SELECT
a.actor_id,
a.first_name,
a.last_name,
GROUP_CONCAT(DISTINCT ||(c.name, ': ',
        (SELECT GROUP_CONCAT(f.title FROM dual ORDER BY f.title SEPARATOR FROM dual ', ')
                    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 a
LEFT JOIN film_actor fa
  ON a.actor_id = fa.actor_id
LEFT JOIN film_category fc
  ON fa.film_id = fc.film_id
LEFT JOIN category c
  ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name;

转换器转换得不太好，所以我不得不修改查询，最后得到如下内容：

    CREATE  VIEW actor_info
AS
SELECT
a.actor_id,
a.first_name,
LISTAGG(c.name || ': ',
        (SELECT LISTAGG(f.title, ', ') within group (order by f.title)
                    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
                 ) 
             ) WITHIN group (order by c.name)
AS film_info
FROM actor a
LEFT JOIN film_actor fa
  ON a.actor_id = fa.actor_id
LEFT JOIN film_category fc
  ON fa.film_id = fc.film_id
LEFT JOIN category c
  ON fc.category_id = c.category_id
GROUP BY a.actor_id, a.first_name, a.last_name, c.name;

但是，我仍然会遇到多个错误，比如我不能在第一个listagg或那个argument should be a constant or a function of expression in GROUP BY中使用argument should be a constant or a function of expression in GROUP BY。我想不出解决这个问题的办法了，有关于错误是什么的建议吗?还是用另一种方法来解决这个问题呢？

Answer 1

甲骨文的listagg()不支持内部独立，这有点麻烦。另一个错误，ORA-30497，是因为您意外地抓狂了，第二个listagg()为第一个错误调用了发送者。

在没有样本数据和预期结果的情况下，很难判断，但您似乎在寻找以下内容：

SELECT a.actor_id, a.first_name, a.last_name,
  LISTAGG(c.name || ': ' || (
    SELECT LISTAGG(f.title, ', ') WITHIN GROUP (ORDER BY f.title)
    FROM film f
    INNER JOIN film_actor fa
    ON fa.film_id = f.film_id
    INNER JOIN film_category fc
    ON f.film_id = fc.film_id
    WHERE fc.category_id = c.category_id -- from main query
    AND fa.actor_id = a.actor_id -- from main query
), '; ') WITHIN GROUP (ORDER BY c.name)
AS film_info
FROM actor a
LEFT JOIN (
  SELECT DISTINCT fa.actor_id, c.category_id, c.name
  FROM film_actor fa
  LEFT JOIN film_category fc
    ON fc.film_id = fa.film_id
  LEFT JOIN category c
  ON c.category_id = fc.category_id
) c
ON c.actor_id = a.actor_id
GROUP BY a.actor_id, a.first_name, a.last_name;

“不同”部分是通过内联视图实现的，而内部listagg则只需要查找该类别/演员的所有影片。