我有一个查询同时涉及左联接和不存在语句。因此,在设计视图中不支持此查询。我想添加另一个表"Table2"中的字段"comments"。对于我创建的查询,这可能吗?代码如下:
SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement
FROM Table1 AS t LEFT JOIN
Table1 AS tprev
ON (tprev.id = t.id) AND (tprev.Date_ < t.Date_)
WHERE not exists
(select 1
from Table1 AS t1
where
t1.ID = t.ID
and t1.Date_ < t.Date_
and t1.Date_ > tprev.Date_);
按照您上一条注释的示例,此查询工作时不会出现任何语法问题:
SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement
FROM (
(
Table2 AS t2
INNER JOIN Table1 AS t
ON (t2.Comments = t.ID)
)
LEFT JOIN Table1 AS tprev
ON tprev.Date_ < t.Date_ AND tprev.id = t.id
)
WHERE not exists (select 1 from Table1 AS t1 where t1.ID = t.ID and t1.Date_ < t.Date_ and t1.Date_ > tprev.Date_);
语法错误的原因是这个括号t.Date_)
太多。
如果它在逻辑上是好的,你应该知道。
有点气味的是:
- 比较
Comments
和ID
。也许应该是CommentID
- 字段名
Date_
。也许你可以找到一个更好的名字,而不必使用下划线
更新
根据这个答案的评论和新的要求,这应该是你需要的:
SELECT
t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement, t2.Comments
FROM
(
Table1 AS t
LEFT JOIN Table2 AS t2
ON t.ID = t2.ID
)
LEFT JOIN Table1 AS tprev
ON (tprev.id = t.id) AND (tprev.Date_ < t.Date_)
WHERE
NOT EXISTS (SELECT 1 FROM Table1 AS t1 WHERE t1.ID = t.ID AND t1.Date_ < t.Date_ AND t1.Date_ > tprev.Date_)
最后是通过另一个左联接来增强对您的问题的查询,从存在相应注释的table2
添加列Comments
。