mysql - 加入动态sql

Question

我有以下按预期工作的动态 sql 查询。但现在我想将结果与对 account_id 的另一个查询的结果结合起来。我通过使查询成为派生表然后加入来尝试通常的方法，但这不起作用(我假设它与所有分号有关)。有没有一种特殊的方法可以将动态 sql 查询与另一个查询连接起来？

    SET @sql = NULL;
  SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(CASE WHEN phone_name = ''',
      phone_name,
      ''' THEN 1 else 0 END) AS `',
      phone_name, '`'
    )
  ) INTO @sql
FROM yt;

SET @sql 
  = CONCAT('SELECT account_id, ', @sql, ' 
            from yt
            group by account_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 1

您能否简单地将其余的查询放在串联中？

...
/* Your existing code */
SET @sql 
  = CONCAT('SELECT account_id, ', @sql, ' 
            from yt
            group by account_id');

/* Now put your derived table stuff in */
SET @sql 
  = CONCAT('select a.account_id, b.something_else
            from some_table as b join (', @sql, ') as a
            on a.account_id = b.account_id');

/* Continue your existing code */
PREPARE stmt FROM @sql;
...