How to write subquery to get the same fields from undetermined tables dynamically-CodePudding

SELECT SUM(t.orgBilledDuration/60) AS Duration
FROM 
(SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_631
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
 UNION ALL
 SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_632
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
 UNION ALL
 SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_633
     WHERE FROM_UNIXTIME(connectTime/1000, '%Y-%m-%d %H:%i:%s') BETWEEN '2021-10-20 00:00:00' AND '2021-12-05 23:59:59'
) t

From the above SQL, I tried to write the following query to select the same fields from undetermined tables dynamically. Following query gives the result from one table dynamically but need to write the above subquery dynamically. Can anyone suggest on this?

Each nested select statement is for month wise tables. Need to search from different month's tables

SET @tab_id:= 631;
SET @tab:= concat('Successfuliptsp.vbSuccessfulCDR_',@tab_id);
SET @ID_1:= 'FROM_UNIXTIME(connectTime/1000, ''%Y-%m-%d %H:%i:%s'') BETWEEN ''2021-11-25 00:00:00'' AND ''2021-11-30 23:59:59''';
SET @s = CONCAT('select SUM(orgBilledDuration)/60 from ', @tab, ' where ', @ID_1); 
PREPARE STMT1 FROM @s; 
EXECUTE STMT1; 
DEALLOCATE PREPARE STMT1;

CodePudding user response：

SET @s1:='SELECT SUM(t.orgBilledDuration/60) AS Duration FROM (';
SET @s2:='SELECT orgBilledDuration FROM Successfuliptsp.vbSuccessfulCDR_';
SET @s3:=' WHERE FROM_UNIXTIME(connectTime/1000, ''%Y-%m-%d'') BETWEEN ''2021-10-20'' AND ''2021-12-05''';
SET @s4:=') t';

SELECT CONCAT(@s1,
              GROUP_CONCAT(CONCAT(@s2,
                                  numbers.tab_no,
                                  @s3)
                           SEPARATOR ' UNION ALL '),
              @s4)
INTO @s
FROM ( SELECT 631 tab_no UNION ALL
       SELECT 632 UNION ALL
       SELECT 633 ) numbers;

PREPARE stmt FROM @s; 
EXECUTE stmt; 
DROP PREPARE stmt;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ee5e3a98a43d70443135ce76bee56d74