在 SQL 子查询中使用某种循环
我正在做一个 SQL 查询,我想在其中获取用户的每个最新条目的一些结果,为此我做了这个查询。
SELECT lA.attendance_id, lA.time_in, lA.time_out, lS.title, lS.end_time, lU.uid
FROM logi_attendance AS lA
INNER JOIN
logi_users AS lU ON lA.user_id = lU.user_id
INNER JOIN
logi_shifts AS lS ON lU.shift_id = lS.shift_id
WHERE
(lA.time_in IS NOT NULL) AND
(lA.time_out IS NULL) AND
(lA.attendance_id =
(
SELECT TOP (1) A.attendance_id
FROM logi_attendance AS A
INNER JOIN
logi_users AS B ON A.user_id = B.user_id
ORDER BY A.attendance_id DESC
)
)
正如您所看到的,我使用了子查询来检索每个用户的最新记录(每天都会创建新记录),因此需要获取最新记录。现在看到我在子查询中使用了 TOP(1),这限制了它只返回 1 条记录。这允许主查询成功运行,但它没有用,因为它只返回它首先找到的一个用户的记录。但我想获得所有用户的结果,但应该以子查询一一返回每个最新id的方式完成,以便主查询成功执行。现在,如果我删除 TOP(1) 那么它会给我一个错误,子查询返回超过 1 个值,这是不正确的。
我不知道我是否正确解释了它,如果您不明白我想问什么,请告诉我,我会尽力解释得更好。
谢谢。
I am making an SQL query in which I want to get some results for each latest entry of user, to do this I have made this query.
SELECT lA.attendance_id, lA.time_in, lA.time_out, lS.title, lS.end_time, lU.uid
FROM logi_attendance AS lA
INNER JOIN
logi_users AS lU ON lA.user_id = lU.user_id
INNER JOIN
logi_shifts AS lS ON lU.shift_id = lS.shift_id
WHERE
(lA.time_in IS NOT NULL) AND
(lA.time_out IS NULL) AND
(lA.attendance_id =
(
SELECT TOP (1) A.attendance_id
FROM logi_attendance AS A
INNER JOIN
logi_users AS B ON A.user_id = B.user_id
ORDER BY A.attendance_id DESC
)
)
As you can see I have used a subquery to retrieve the latest records of each user (new records are created on daily basis) so getting the latest record is requirement. Now see that I have used TOP(1) in subquery which restrict it to return 1 record only. This allows the main query to run successfully but it is not useful because it only returns record of one user which it finds first. But I want to get results of all users, but it should be done in a way that subquery returns each latest id one by one so the main query executes successfully. Right now if I remove TOP(1) then it gives me an error that subquery returns more than 1 value which is incorrect.
I don't know if I explained it properly, let me know if you don't understand what I am trying to ask and I will try to explain better.
Thanks.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您应该能够将
WHERE A.user_id = lA.user_id
添加到子查询中。这将为您提供每个用户的最新条目。
You should be able to add
WHERE A.user_id = lA.user_id
to your sub-query. This would get you the most recent entry per user.
不能 100% 确定解决方案,但它需要 TOP 1 的原因是因为您测试
lA.attendance_id = some
并且某些内容只能是 1 个结果。尝试将其更改为
lA.attendance_id IN (select xxxxx)
,这应该会为您带来更多结果。Not 100% sure of a solution but the reason it needs the TOP 1 is because you test
lA.attendance_id = something
and that something has to only be 1 result.Try changing it to
lA.attendance_id IN (select xxxxx)
and that should get you more results.您的子查询不相关。您需要一个相关子查询,如下所示:
Your subquery is not correlated. You need a correlated subquery, as follows: