求助一个 SQL 优化

[xuecat]

SELECT a.* FROM `tblog` as a 
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID` 
INNER JOIN (SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100) as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION ALL
(SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100)

如图，有括号的俩句实际都是一个查询。所以我想优化成一句 这是我想象的语法，但是就是跑不通，所以让大佬看看呢

SELECT a.* FROM `tblog` as a 
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID` 
INNER JOIN (SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100) as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION ALL
f

[lianyue]

手写 sql ？
这不影响性能 优化他干嘛。

[lianyue]

https://www.google.com/search?q=sql+%E5%A4%8D%E7%94%A8+with

[xuecat]

影响啊，因为有俩句一模一样的查询，我时间就 double 了。所以我想看看能不能只查一次

[codingadog]

mysql8 以上可以用 with 语句抽出公共部分，低版本就不知道了。

[mightybruce]

mysql 将这个查询语句创建为一个视图，
这两句改写为在视图上操作。

[dog82]

用 with 子句

[declandragon]

CREATE TEMPORARY TABLE TempTable AS
(SELECT * FROM `tblog` WHERE `ModID`=357 AND `ID`<2147483647 ORDER BY `ID` desc limit 100);

SELECT a.* FROM `tblog` as a
INNER JOIN `structroot` as b ON a.`ModID`=b.`ModuleID` AND a.`SrcGuid`=b.`DataGUID`
INNER JOIN TempTable as f ON f.`ModID`=b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID`<2147483647
UNION
SELECT * FROM TempTable;

[yidinghe]

@xuecat 耗时 double 的原因在于 UNION ALL ，所以即便用 WITH 提取出来，恐怕也无济于事。

[alwaysdazz]

WITH FilteredTBlog AS (
SELECT * FROM `tblog`
WHERE `ModID`=357 AND `ID`<2147483647
ORDER BY `ID` DESC
LIMIT 100
)
SELECT a.*
FROM `tblog` AS a
INNER JOIN `structroot` AS b ON a.`ModID` = b.`ModuleID` AND a.`SrcGuid` = b.`DataGUID`
INNER JOIN FilteredTBlog AS f ON f.`ModID` = b.`RootModuleID` AND f.`SrcGuid` = b.`RootDataGUID`
WHERE a.`ID` < 2147483647
UNION ALL
SELECT * FROM FilteredTBlog