mysql - 从生成的大型查询中修剪尾部 mysql group_concat

Question

我已经完成了大部分工作，但现在我要修剪最后一点，但我陷入了困境。

计划是从此输出语句中删除最后一个 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 之前的最后一个 UNION ALL。

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

Answer 1

您可以通过从 GROUP_CONCAT 中的最后一个元素中删除 UNION ALL 并将 SEPARATOR 更改为 UNION ALL 即轻松解决此问题

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`"  
                           SEPARATOR ' UNION ALL '))
               ')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%'