mysql - 连接两个 Substring_index

Question

我需要连接下面选择子字符串的 2 个结果。有什么想法吗？

BEGIN
select SUBSTRING_INDEX(SUBSTRING_INDEX(oldVal, '-', n.n), '-', -1) AS oldVal5
FROM audits oldVal
CROSS JOIN ( SELECT a.N + b.N * 10 + 1 n 
FROM (SELECT 0 AS N 
      UNION ALL SELECT 1 
      UNION ALL SELECT 2 
      UNION ALL SELECT 3 
      UNION ALL SELECT 4      
      UNION ALL SELECT 5 
      UNION ALL SELECT 6 
      UNION ALL SELECT 7 
      UNION ALL SELECT 8 
      UNION ALL SELECT 9) a ,
   (SELECT 0 AS N 
      UNION ALL SELECT 1 
      UNION ALL SELECT 2 
      UNION ALL SELECT 3 
      UNION ALL SELECT 4 
      UNION ALL SELECT 5 
      UNION ALL SELECT 6 
      UNION ALL SELECT 7 
      UNION ALL SELECT 8 
      UNION ALL SELECT 9) b ORDER BY n ) n 
WHERE n.n <= 1 + (LENGTH(oldVal) - LENGTH(REPLACE(oldVal, '-', ''))) and 
dbUser=34 and tableName='persona' and task='u';

select SUBSTRING_INDEX(SUBSTRING_INDEX(newVal, '-', n.n), '-', -1) AS newval5 
FROM audits newVal
CROSS JOIN ( SELECT a.N + b.N * 10 + 1 n 
FROM (SELECT 0 AS N 
UNION ALL SELECT 1 
UNION ALL SELECT 2 
UNION ALL SELECT 3 
UNION ALL SELECT 4 
UNION ALL SELECT 5 
UNION ALL SELECT 6 
UNION ALL SELECT 7 
UNION ALL SELECT 8 
UNION ALL SELECT 9) a ,
(SELECT 0 AS N 
UNION ALL SELECT 1 
UNION ALL SELECT 2 
UNION ALL SELECT 3 
UNION ALL SELECT 4 
UNION ALL SELECT 5 
UNION ALL SELECT 6 
UNION ALL SELECT 7 
UNION ALL SELECT 8 
UNION ALL SELECT 9) b ORDER BY n ) n 
WHERE n.n <= 1 + (LENGTH(newVal) - LENGTH(REPLACE(newVal, '-', ''))) and 
dbUser=34 and tableName='persona' and task='u';

END

Answer 1

只需在同一查询中选择两者即可。

select SUBSTRING_INDEX(SUBSTRING_INDEX(oldVal, '-', n.n), '-', -1) AS oldVal5,
       SUBSTRING_INDEX(SUBSTRING_INDEX(newVal, '-', n.n), '-', -1) AS newVal5
FROM audits oldVal
CROSS JOIN ( SELECT a.N + b.N * 10 + 1 n 
FROM (SELECT 0 AS N 
      UNION ALL SELECT 1 
      UNION ALL SELECT 2 
      UNION ALL SELECT 3 
      UNION ALL SELECT 4      
      UNION ALL SELECT 5 
      UNION ALL SELECT 6 
      UNION ALL SELECT 7 
      UNION ALL SELECT 8 
      UNION ALL SELECT 9) a ,
   (SELECT 0 AS N 
      UNION ALL SELECT 1 
      UNION ALL SELECT 2 
      UNION ALL SELECT 3 
      UNION ALL SELECT 4 
      UNION ALL SELECT 5 
      UNION ALL SELECT 6 
      UNION ALL SELECT 7 
      UNION ALL SELECT 8 
      UNION ALL SELECT 9) b ORDER BY n ) n 
WHERE (n.n <= 1 + (LENGTH(oldVal) - LENGTH(REPLACE(oldVal, '-', ''))) 
        or
       n.n <= 1 + (LENGTH(newVal) - LENGTH(REPLACE(newVal, '-', ''))))
    and dbUser=34 and tableName='persona' and task='u';