mysql - 将其重写为不包含子查询，以便我可以添加到 View

Question

请原谅我的无知——我在学习 MySQL 的同时，我真的被困在这个问题上:

我刚刚让这个查询正常工作，但是当我尝试将它添加到 View 时，我需要它在其中运行，但由于子查询，我做不到。我无法弄清楚如何避免子查询 - 你们中任何知识渊博的人都可以指出我正确的方向吗？

(SELECT `clients`.`Name` AS `ClientName`, `computers`.`Name` AS `ComputerName`, BackupName, results.Source, results.Result, results.Message, results.TimeGen, results.EventID
FROM 
(
SELECT DISTINCT 
  eventlogs.`ComputerID`, (
    CASE
      WHEN `eventlogs`.message LIKE '% job %'     
    THEN SUBSTR(`eventlogs`.`Message`, (LOCATE(' job ', `eventlogs`.`Message`) + 6), ((LOCATE('finished', `eventlogs`.`Message`) - LOCATE(' job ', `eventlogs`.`Message`)) - 8)) 
      ELSE NULL 
    END
  ) AS `BackupName`, `eventlogs`.`Source` AS `Source`, (
    CASE
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Success%'
    THEN 'Pass' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Warning%'
    THEN 'Pass-Warn' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Fail%'      
    THEN 'FAIL' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Error%'
    THEN 'FAIL' 
      ELSE 'Unknown - ASK ALEX!' 
    END
  ) AS `Result`, `eventlogs`.`Message` AS `Message`, `eventlogs`.`TimeGen` AS `TimeGen`, `eventlogs`.`EventID` AS `EventID` 

  FROM `eventlogs` 

  WHERE (
    `eventlogs`.`TimeGen` >= (NOW() - INTERVAL 1 DAY) 
    AND eventlogs.source = "Veeam Endpoint Backup" 
    AND EventID < 10000
    AND EventID != 110
    AND EventID != 1
  )
) AS results
INNER JOIN
(
  SELECT eventlogs.`ComputerID`,
  (CASE
  WHEN `eventlogs`.message LIKE '% job %'     
THEN SUBSTR(`eventlogs`.`Message`, (LOCATE(' job ', `eventlogs`.`Message`) + 6), ((LOCATE('finished', `eventlogs`.`Message`) - LOCATE(' job ', `eventlogs`.`Message`)) - 8)) 
  ELSE NULL 
END) AS `BackupName`, `eventlogs`.`Source` AS `Source`, eventlogs.Message, MAX(TimeGen) AS TimeGen, EventID
 FROM   `eventlogs` 

WHERE (
`eventlogs`.`TimeGen` >= (NOW() - INTERVAL 1 DAY) 
AND eventlogs.source = "Veeam Endpoint Backup" 
AND EventID < 10000
AND EventID != 110
AND EventID != 1 )
 GROUP BY ComputerID, Source, BackupName) AS latest USING (ComputerID, Source, BackupName, TimeGen)
JOIN `computers` USING (ComputerID)
JOIN `clients` USING (ClientID)
)

基本上对于每一个(计算机+BackupName)我都需要最近的事件，然后对它进行判断并输出结果。不幸的是，BackupName 是从 eventlogs.Message 列中的文本解析而来的。

抱歉，它有点长。欢迎任何帮助或建议 - 谢谢!

Answer 1

意识到我可以完全重写并仅将判断应用于“最新”数据集中的数据，而无需所有复杂性。只希望这在实践中确实能达到我想要的效果，哈哈。谢谢戈登。

SELECT `clients`.`Name` AS `ClientName`, `computers`.`Name` AS `ComputerName`, (CASE
  WHEN `eventlogs`.message LIKE '% job %'     
THEN SUBSTR(`eventlogs`.`Message`, (LOCATE(' job ', `eventlogs`.`Message`) + 6), ((LOCATE('finished', `eventlogs`.`Message`) - LOCATE(' job ', `eventlogs`.`Message`)) - 8)) 
  ELSE NULL 
END) AS `BackupName`, `eventlogs`.`Source` AS `Source`, (
    CASE
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Success%'
    THEN 'Pass' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Warning%'
    THEN 'Pass-Warn' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Fail%'      
    THEN 'FAIL' 
      WHEN `eventlogs`.`message` LIKE 'Endpoint%Backup job % finished with Error%'
    THEN 'FAIL' 
      ELSE 'Unknown - ASK ALEX!' 
    END
  ) AS `Result`, eventlogs.Message, MAX(TimeGen) AS TimeGen, EventID
FROM    `eventlogs`  JOIN `computers` USING (ComputerID)
JOIN `clients` USING (ClientID)
WHERE (
`eventlogs`.`TimeGen` >= (NOW() - INTERVAL 1 DAY) 
AND eventlogs.source = "Veeam Endpoint Backup" 
AND EventID < 10000
AND EventID != 110
AND EventID != 1 )
GROUP BY ComputerID, Source, BackupName