英文:
SQL Query, loop through data
问题
我有一个具有以下字段的数据库：
UserID，DateRecorded和Score。
每次更新分数时，都会向表中添加新条目。以下是用户记录的示例：
UserID | DateRecorded | Score
3 | 21-08-2019 | 10
3 | 23-08-2019 | 5
3 | 21-09-2019 | 10
3 | 21-08-2018 | 1
从查询中，我希望只显示具有最早DateRecorded的所有用户的分数。
尽管对游标不太熟悉，但我尝试使用网上的示例并根据我的情况进行了调整，但似乎没有奏效。以下是我的游标尝试：
DECLARE @DateRecorded AS DATETIME;
DECLARE @scores AS FLOAT;
DECLARE @Cursor as CURSOR;
SET @Cursor = CURSOR FOR
select [user_id], [DateRecorded], scores
from myTable
where [user_id] = '55';
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @UserID AS 'User ID', MIN(@DateRecorded) AS 'Date', @scores AS 'Score';
FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;
END
CLOSE @Cursor;
DEALLOCATE @Cursor;
这是我期望的结果：
3 | 21-08-2018 | 1
是否有比使用游标更简单的方法？
我完全误解了游标吗？
感谢您的帮助
英文:
I have a database with the following fields:
UserID, DateRecorded and Score.
A new entry is added to the table each time the score is updated. Here is an example of a user record:
UserID | DateRecorded | Score
3 | 21-08-2019 | 10
3 | 23-08-2019 | 5
3 | 21-09-2019 | 10
3 | 21-08-2018 | 1
From the query I would like it to show me only the scores of all users with the earliest DateRecorded.
Although not much familiar with cursors, I tried using an example from the net and adapted it to mine, but that doesn't seem to have worked. Here is my attempt with the cursor:
DECLARE @DateRecorded AS DATETIME;
DECLARE @scores AS FLOAT;
DECLARE @Cursor as CURSOR;
SET @Cursor = CURSOR FOR
select [user_id], [DateRecorded], scores
from myTable
where [user_id] = '55'
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @UserID AS 'User ID', MIN(@DateRecorded) AS 'Date', @scores AS 'Score';
FETCH NEXT FROM @Cursor INTO @UserID, @DateRecorded, @scores;
END
CLOSE @Cursor;
DEALLOCATE @Cursor;
This is the results I'm expecting:
3 | 21-08-2018 | 1
Is there a simpler way of doing this than using cursors?
Have I completely missunderstood cursors?
Thanks for your help
答案1
得分: 1
不要使用游标和循环（除非没有其他选择）：它们很复杂，而且非常慢。您在此处所要求的可以通过纯SQL查询来完成。
如果您只想要每个用户的最早记录日期而不需要其他信息，那么简单的聚合就足够了：
select UserIdD, min(DateRecorded) from mytable group by UserID
如果您想要每个用户的整个“第一”条记录，那么这是一个典型的最大-N-每组问题。一种方法是使用相关子查询进行过滤：
select t.*
from mytable t
where t.DateRecorded = (
select min(t1.DateRecorded) from mytable t1 where t1.UserID = t.UserID
)
您可能还会喜欢反向left join解决方案：
select t.*
from mytable t
left join mytable t1 on t1.UserID = t.UserID and t1.DateRecorded < t.DateRecorded
where t1.UserID is null
英文:
Don't use cursors and loops (unless you have no other choice): they are complicated, and very slow. What you are asking for here can be done with straight SQL querires.
If you just want the earliest record date per user and no other information, then simple aggregation is enough:
select UserIdD, min(DateRecorded) from mytable group by UserID
If you want the entire "first" record per user, then that's a typical greatest-n-per-group problem. One method is to filter with a correlated subquery:
select t.*
from mytable t
where t.DateRecorded = (
select min(t1.DateRecorded) from mytable t1 where t1.UserID = t.UserID
)
You might also like the anti-left join solution:
select t.*
from mytable t
left join mytable t1 on t1.UserID = t.UserID and t1.DateRecorded < t.DateRecorded
where t1.UserID is null
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论