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
從上面的 SQL 中，我嘗試撰寫以下查詢來動態地從未確定的表中選擇相同的欄位。以下查詢動態給出一張表的結果，但需要動態撰寫上述子查詢。任何人都可以對此提出建議嗎？
每個嵌套的 select 陳述句都用于按月計算的表。需要從不同月份的表中搜索
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;
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/396724.html
上一篇：比較同一表中同一行的主ID