从大型生成的查询中修剪后面的mysql group_concat

我大部分时间都在工作,但现在我只能把最后一点修剪掉,我被卡住了。

计划是从这个输出语句中删除最后一个UNION ALL,这样我就可以将整个内容复制到MySQL中并获得输出。是的,我知道一个SPROC或准备好的声明是最好的,但安全部门对此表示不满,我们会对此进行审计。

此查询的原因是我有多个具有不同sys_log000x表的数据库。有些从0000到0005或0006,其他的可以高达9999,所以我需要一个查询,将所有表聚合为可用格式。

以下是用于生成完整查询的查询:

SET SESSION group_concat_max_len = 1024*1024*1024; 
SELECT CONCAT("SELECT a.`table`, a.parm1 AS `updatedColumn(s)`, a.parm2 AS `DDLtype`, SUM(a.qtyReady) AS `qtyReady`, SUM(a.qtyQueued) AS `qtyQueued`, SUM(a.qtyProcessed) AS `qtyProcessed`, SUM(a.qtyTransferred) AS `qtyTransferred`, SUM(a.qtyError) AS `qtyError`, a.sys_updated_by AS `updatedBy`, SUM(COUNT) AS `TtlCount` FROM (",
             (GROUP_CONCAT("SELECT ", 
                           TABLE_NAME, 
                           "0.`table` AS 'table',LEFT(", 
                           TABLE_NAME, 
                           "0.`parm1`,40) AS 'parm1',LEFT(", 
                           TABLE_NAME, 
                           "0.`parm2`,40) AS 'parm2',SUM(CASE WHEN ", 
                           TABLE_NAME, 
                           "0.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN ", 
                           TABLE_NAME, 
                           "0.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN ", 
                           TABLE_NAME, 
                           "0.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN ",
                           TABLE_NAME, 
                           "0.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN ",
                           TABLE_NAME, 
                           "0.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,", 
                           TABLE_NAME,
                           "0.`sys_updated_by` AS 'sys_updated_by', COUNT(", 
                           TABLE_NAME, 
                           "0.`sys_id`) AS `count` FROM ",
                           TABLE_NAME, 
                           " ", 
                           TABLE_NAME,
                           "0", " WHERE ", 
                           TABLE_NAME, 
                           "0.`queue` = 'text_index' GROUP BY ",
                           TABLE_NAME,
                           "0.`table`,", 
                           TABLE_NAME, 
                           "0.`parm1`,", 
                           TABLE_NAME,
                           "0.`parm2`,",
                           TABLE_NAME,
                           "0.`sys_updated_by UNION ALL`"  
                           SEPARATOR ' '))
               ')a GROUP BY a.`table`, a.`parm1`,  a.`parm2`,  a.`sys_updated_by` ORDER BY `TtlCount` DESC LIMIT 40;')
FROM information_schema.tables 
WHERE table_schema !='mysql' AND table_name LIKE 'sys_log0%'

下面是经过删减的输出:

SELECT a.`table`, a.parm1 AS `updatedColumn(s)`, a.parm2 AS `DDLtype`, SUM(a.qtyReady) AS `qtyReady`, SUM(a.qtyQueued) AS `qtyQueued`, SUM(a.qtyProcessed) AS `qtyProcessed`, SUM(a.qtyTransferred) AS `qtyTransferred`, SUM(a.qtyError) AS `qtyError`, a.sys_updated_by AS `updatedBy`, SUM(COUNT) AS `TtlCount` FROM (SELECT sys_log00000.`table` AS 'table',LEFT(sys_log00000.`parm1`,40) AS 'parm1',LEFT(sys_log00000.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00000.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00000.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00000.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00000.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00000.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00000.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00000.`sys_id`) AS `count` FROM sys_log0000 sys_log00000 WHERE sys_log00000.`queue` = 'text_index' GROUP BY sys_log00000.`table`,sys_log00000.`parm1`,sys_log00000.`parm2`,sys_log00000.`sys_updated_by` UNION ALL SELECT sys_log00010.`table` AS 'table',LEFT(sys_log00010.`parm1`,40) AS 'parm1',LEFT(sys_log00010.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00010.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00010.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00010.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00010.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00010.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00010.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00010.`sys_id`) AS `count` FROM sys_log0001 sys_log00010 WHERE sys_log00010.`queue` = 'text_index' GROUP BY sys_log00010.`table`,sys_log00010.`parm1`,sys_log00010.`parm2`,sys_log00010.`sys_updated_by` UNION ALL SELECT sys_log00020.`table` AS 'table',LEFT(sys_log00020.`parm1`,40) AS 'parm1',LEFT(sys_log00020.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00020.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00020.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00020.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00020.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00020.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00020.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00020.`sys_id`) AS `count` FROM sys_log0002 sys_log00020 WHERE sys_log00020.`queue` = 'text_index' GROUP BY sys_log00020.`table`,sys_log00020.`parm1`,sys_log00020.`parm2`,sys_log00020.`sys_updated_by` UNION ALL SELECT sys_log00030.`table` AS 'table',LEFT(sys_log00030.`parm1`,40) AS 'parm1',LEFT(sys_log00030.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00030.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00030.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00030.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00030.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00030.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00030.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00030.`sys_id`) AS `count` FROM sys_log0003 sys_log00030 WHERE sys_log00030.`queue` = 'text_index' GROUP BY sys_log00030.`table`,sys_log00030.`parm1`,sys_log00030.`parm2`,sys_log00030.`sys_updated_by` UNION ALL SELECT sys_log00040.`table` AS 'table',LEFT(sys_log00040.`parm1`,40) AS 'parm1',LEFT(sys_log00040.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00040.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00040.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00040.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00040.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00040.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00040.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00040.`sys_id`) AS `count` FROM sys_log0004 sys_log00040 WHERE sys_log00040.`queue` = 'text_index' GROUP BY sys_log00040.`table`,sys_log00040.`parm1`,sys_log00040.`parm2`,sys_log00040.`sys_updated_by` UNION ALL SELECT sys_log00050.`table` AS 'table',LEFT(sys_log00050.`parm1`,40) AS 'parm1',LEFT(sys_log00050.`parm2`,40) AS 'parm2',SUM(CASE WHEN sys_log00050.`state` = 'ready' THEN 1 ELSE 0 END)AS `qtyReady`,SUM(CASE WHEN sys_log00050.`state` LIKE 'queued%' THEN 1 ELSE 0 END)AS `qtyQueued`,SUM(CASE WHEN sys_log00050.`state` = 'processed' THEN 1 ELSE 0 END)AS `qtyProcessed`,SUM(CASE WHEN sys_log00050.`state` = 'transferred' THEN 1 ELSE 0 END)AS `qtyTransferred`,SUM(CASE WHEN sys_log00050.`state` = 'error' THEN 1 ELSE 0 END)AS `qtyError`,sys_log00050.`sys_updated_by` AS 'sys_updated_by', COUNT(sys_log00050.`sys_id`) AS `count` FROM sys_log0005 sys_log00050 WHERE sys_log00050.`queue` = 'text_index' GROUP BY sys_log00050.`table`,sys_log00050.`parm1`,sys_log00050.`parm2`,sys_log00050.`sys_updated_by` UNION ALL)a GROUP BY a.`table`, a.`parm1`, a.`parm2`, a.`sys_updated_by` ORDER BY `TtlCount` DESC LIMIT 40;

上一个工会就在)a是我要删除的。

你能提供的任何帮助或指导都会很棒!