如何获得每个用户的第一个条目，包括条目ID

I am attempting to write a query.
From the entries table below, I would like returned one row for each user. The row returned includes entries from computer 1 and computer 2. The row returned should have the lowest date. The resultes are ordered by date asc.

我正在尝试编写查询。从下面的条目表中,我想为每个用户返回一行。返回的行包括来自计算机1和计算机2的条目。返回的行应具有最低日期。结果按日期asc排序。

Entries: | EntryID | UserID | Date | Value | ComputerID | |---------|---------|------------|--------------|------------| | 1 | 21 | 01/03/2013 | Login | 3 | | 2 | 22 | 01/04/2013 | Login | 1 | | 3 | 21 | 01/05/2013 | Edit | 3 | | 4 | 20 | 01/06/2013 | Login | 2 | | 5 | 20 | 01/07/2013 | Search | 2 | | 6 | 22 | 01/08/2013 | Login | 4 | | 7 | 21 | 01/09/2013 | Close | 3 | | 8 | 21 | 01/11/2013 | Login | 1 | | 9 | 20 | 01/12/2013 | Edit | 2 | | 10 | 22 | 01/13/2013 | Search | 1 |

This is the desired result of the query I am attempting to write: |Userid | First Log Date | EntryID | ComputerID | |-------|----------------|---------|------------| | 22 | 01/04/2013 | 2 | 1 | | 20 | 01/06/2013 | 4 | 2 | | 21 | 01/11/2013 | 8 | 1 |

参赛作品:| EntryID |用户ID |日期|价值| ComputerID | | --------- | --------- | ------------ | -------------- | - ----------- | | 1 | 21 | 01/03/2013 |登录| 3 | | 2 | 22 | 01/04/2013 |登录| 1 | | 3 | 21 | 01/05/2013 |编辑| 3 | | 4 | 20 | 01/06/2013 |登录| 2 | | 5 | 20 | 01/07/2013 |搜索| 2 | | 6 | 22 | 01/08/2013 |登录| 4 | | 7 | 21 | 01/09/2013 |关闭| 3 | | 8 | 21 | 01/11/2013 |登录| 1 | | 9 | 20 | 01/12/2013 |编辑| 2 | | 10 | 22 | 01/13/2013 |搜索| 1 |这是我试图写的查询的期望结果:| Userid |第一个日志日期| EntryID | ComputerID | | ------- | ---------------- | --------- | ------------ | | 22 | 01/04/2013 | 2 | 1 | | 20 | 01/06/2013 | 4 | 2 | | 21 | 01/11/2013 | 8 | 1 |

EDIT: I have tried the following but I have trouble getting the correct EntryID:

编辑:我尝试了以下但我无法获得正确的EntryID:

SELECT max(userID) as user, 
min(date) as date, 
max(entryID) as entryID, 
max(computerID) as computerID 
From Entry where computerID in ('1', '2') group by userID order by date ASC

2 个解决方案

#1

I see you flagged this as hana. I'm going to guess you haven't had to work with relational databases much.

我看到你把这个标记为hana。我猜你不必多关注数据库。

Relational databases operate on data sets. row level or record level processing is inefficient. So the trick here is to think of two different sets of data.

关系数据库对数据集进行操作。行级或记录级处理效率低下。所以这里的诀窍是考虑两组不同的数据。

1 set containing earliest entries (subset of set 2) for a user and computer

1组包含用户和计算机的最早条目(组2的子集)

2 set containing all the records

2套包含所有记录

You first need to generate a subset of data based listing the earliest entry for user on a computer. This is where you use your aggregate function (min) on date and group by user and computer.

您首先需要生成一个基于数据的子集,列出计算机上用户的最早条目。这是您在日期和用户和计算机上分组使用聚合函数(min)的位置。

You then join this back to the base set to limit the list to the earliest access on a computer for each user.

然后,将其连接回基本集,将列表限制为计算机上每个用户最早的访问权限。

As such:

SELECT A.UserID, A.Date as FirstLogDate, A.EntryID, A.ComputerID
FROM entries A
INNER JOIN (SELECT min(Date) mDate, UserID, ComputerID 
            FROM entries 
            GROUP BY userID, computerID) B
 on A.Date = B.mDate
 and A.userID = B.UserID
 and A.computerID=B.ComputerID
WHERE A.ComputerID in (1,2)

You could put the where clause on sub query and you may get some gain in efficiency as the system would limit the data before the join.

您可以将where子句放在子查询上,并且您可以获得一些效率提升,因为系统会在连接之前限制数据。

#2

You can filter by the min date, getting it from a subquery. This subquery is itself filtered by the main query (in this case, by Userid). Like this:

您可以按最小日期过滤,从子查询中获取它。此子查询本身由主查询过滤(在本例中,由Userid过滤)。喜欢这个:

select 
    Userid, Date as FirstLogDate, EntryID, ComputerID
from
    Entries as A
where 
    ComputerID in (1, 2)
    and Date =
        (select
            min(date)
        from
            Entries
        where
            Userid = A.Userid)

#1