基于计数结果和条件更新的Access SQL更新
基于计数结果和条件更新的Access SQL更新
提问于 2012-08-15 04:33:04
昨天，我得到了关于这条SQL语句的帮助，它工作得非常棒……现在，我正尝试使用该查询中的数据来更新另一个表上的两列。
下面是我得到的查询：
SELECT x.TechID,
Count(*) AS cnt,
tblEmployeeData.LName,
tblEmployeeData.Pernr,
tblEmployeeData.Occurrences,
tblEmployeeData.Standing
FROM tblEmployeeData
INNER JOIN tblOccurrence AS x
ON tblEmployeeData.TechID = x.TechID
WHERE ( ( ( x.OccurrenceDate ) BETWEEN Dateadd("m", -6, Date()) AND Date() )
AND ( ( EXISTS (SELECT *
FROM tblOccurrence AS y
WHERE y.TechID = x.TechID
AND Dateadd ("d", -1, x.[OccurrenceDate]) = y.[OccurrenceDate]) ) = False ) )
GROUP BY x.TechID,
tblEmployeeData.LName,
tblEmployeeData.Pernr;我想要做的是获取结果并更新tblEmployeeData两列。其中一列(tblEmployeeData.Occorrences)将是由'cnt‘表示的值...然后最难的部分..。将使用'cnt‘中的值更新列tblEmployeeData.Standing，如下所示：
0-3 = "Good"
4-5 = "Verbal Warning"
6-7 = "Written Warning"
8 = "Final Written Warning"
9+ = "Termination" 它已经是一个很大的SQL语句了，这让我摸不着头脑！
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-08-15 04:46:03
您可以将一个查询包装在另一个查询中：
SELECT TechID, Rank FROM Rank,
(SELECT x.TechID, Count(*) AS cnt, tblEmployeeData.LName,
tblEmployeeData.Pernr, tblEmployeeData.Occurrences, tblEmployeeData.Standing
FROM tblEmployeeData
INNER JOIN tblOccurrence AS x ON tblEmployeeData.TechID = x.TechID
WHERE (((x.OccurrenceDate) Between DateAdd("m",-6,Date()) And Date())
AND ((Exists
(SELECT * FROM tblOccurrence AS y WHERE y.TechID = x.TechID AND DATEADD
("d", -1, x.[OccurrenceDate]) = y.[OccurrenceDate]))=False))
GROUP BY x.TechID, tblEmployeeData.LName, tblEmployeeData.Pernr) a
WHERE a.Cnt BETWEEN Rank.Low And rank.High其思想是将查询与Rank表一起使用，如下所示：
Low High Rank
0 3 Good
4 5 Verbal Warning
6 7 Written Warning
8 8 Final Written Warning
9 99 Termination编辑重新注释
这为我在一个粗略的模型中运行
SELECT a.TechID, tblRank.Rank FROM tblRank, (SELECT x.TechID, Count(*) AS cnt, tblEmployeeData.LName,
tblEmployeeData.Pernr, tblEmployeeData.Occurrences, tblEmployeeData.Standing
FROM tblEmployeeData
INNER JOIN tblOccurrence AS x ON tblEmployeeData.TechID = x.TechID
WHERE (((x.OccurrenceDate) Between DateAdd("m",-6,Date()) And Date()) AND ((Exists
(SELECT * FROM tblOccurrence AS y WHERE y.TechID = x.TechID AND DATEADD
("d", -1, x.[OccurrenceDate]) = y.[OccurrenceDate]))=False))
GROUP BY x.TechID, tblEmployeeData.LName, tblEmployeeData.Pernr, tblEmployeeData.Occurrences, tblEmployeeData.Standing) a
WHERE a.Cnt BETWEEN tblRank.Low And tblrank.High页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11960289
复制相关文章
相似问题
腾讯云开发者
