MySQL -基于多个表的SELECT查询编写更新查询
MySQL -基于多个表的SELECT查询编写更新查询
提问于 2015-02-11 15:40:42
我在MySQL或数据库方面远远不够强大，所以我使用了一个名为FlySpeed SQL查询的工具。这个工具帮助我以图形方式创建了MySQL查询。下面是我用这个工具创建的查询，以及在互联网上阅读的内容。
Select
Employee.Firstname As Prénom,
Employee.Name As NOM,
TimeSheet.Filled As Validé,
TimeSheet.Closed As Clôturé,
Sum(Imputation.Hours) As `Somme des heures`,
TimeSheet.Month + 1 As Mois,
TimeSheet.Year As Année
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
TimeSheet.Closed Is Null) And
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01') And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
Order By
Année,
Mois,
NOM此查询将准确地返回所需的结果。保持与上面的MySQL查询相同的条件，我希望将关闭字段更新为"1“。我想做一些有味道的东西：
-- UPDATE Query
--
UPDATE TimeSheet
SET Closed = '1'
--
-- UPDATE Query
From
Employee Inner Join
TimeSheet On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
--
-- With those conditions
--
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
TimeSheet.Closed Is Null) And
-- Calculating a time range
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')
And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
-- Calculation : >= 5 times the number of days in the period
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
---
-- With those conditions因此，我需要帮助将SELECT查询转换为更新查询。你可以问我更多的信息。
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-03-18 08:58:13
我终于找到了解决问题的办法。
这篇文章对我帮助很大：http://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO
下面是：
UPDATE TimeSheet
SET
Closed = '1'
WHERE
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
(SELECT
TimeSheet.Id
FROM
TimeSheet
Where
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
Imputation
INNER JOIN TimeSheet ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
(TimeSheet.Closed = '0'
OR TimeSheet.Closed IS NULL)
AND TimeSheet.Filled = '1'
AND Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
GROUP BY TimeSheet.Id
HAVING TimeSheet.Id NOT IN (SELECT DISTINCT
TimeSheet.Id
FROM
TimeSheet
INNER JOIN Imputation ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
AND Project.Id = '1')
AND Sum(Imputation.Hours) >= 5 * (DateDiff(Last_Day(Current_Date - INterval 1 Month), Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')) + 1))) VirtualTable01);然而，“解决方案”一词或许有点强烈。我想更多地说，这是一个解决办法。因此，我不确定这个请求是否经过了很好的优化，并且满足了标准，但至少它有优势给我想要的结果。
与上述查询相同，但注释如下：
-- UPDATE Query
--
UPDATE TimeSheet
SET
Closed = '1'
--
-- UPDATE Query
--
-- With those conditions
--
WHERE
-- First Select
--
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
-- Second Select
--
(SELECT
TimeSheet.Id
FROM
TimeSheet
Where
-- Third Select
--
TimeSheet.Id IN (SELECT
TimeSheet.Id
FROM
Imputation
INNER JOIN TimeSheet ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
(TimeSheet.Closed = '0'
OR TimeSheet.Closed IS NULL)
AND TimeSheet.Filled = '1'
AND Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
GROUP BY TimeSheet.Id
-- Fourth Select
--
HAVING TimeSheet.Id NOT IN (SELECT DISTINCT
TimeSheet.Id
FROM
TimeSheet
INNER JOIN Imputation ON Imputation.TimeSheet_Id = TimeSheet.Id
INNER JOIN Project ON Imputation.Project_Id = Project.Id
INNER JOIN Employee ON TimeSheet.Employee_Id = Employee.Id
Where
Imputation.Day <= Last_Day(Current_Date - INterval 1 Month)
AND Imputation.Day >= Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')
AND Project.Id = '1')
--
-- End Fourth Select
AND Sum(Imputation.Hours) >= 5 * (DateDiff(Last_Day(Current_Date - INterval 1 Month), Date_Format(Current_Date - INterval 1 Month, '%Y-%m-01')) + 1))
--
-- End Third Select
) VirtualTable01)
--
-- End Second Select
--
-- End First Select
;Stack Overflow用户
发布于 2015-02-11 15:49:00
尝试在set之后移动joins操作
UPDATE TimeSheet Inner Join
Employee On TimeSheet.Employee_Id = Employee.Id Inner Join
Imputation On Imputation.TimeSheet_Id = TimeSheet.Id Inner Join
Project On Imputation.Project_Id = Project.Id
--
-- With those conditions
--
SET TimeSheet.Closed = '1'
Where
TimeSheet.Filled = '1' And
(TimeSheet.Closed = '0' Or
TimeSheet.Closed Is Null) And
-- Calculating a time range
Imputation.Day <= Last_Day(Current_Date - Interval 1 Month) And Imputation.Day >= Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')
And
Project.Id != '1'
Group By
Employee.Name, TimeSheet.Month + 1, TimeSheet.Year
Having
-- Calculation : >= 5 times the number of days in the period
Sum(Imputation.Hours) >= 5 * ((DateDiff(Last_Day(Current_Date - Interval 1 Month), Date_Format(Current_Date - Interval 1 Month, '%Y-%m-01')) + 1))
---页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28458386
复制相关文章
相似问题
腾讯云开发者
