从我的数据集中提取计数和最早的实例
使用 Microsoft Sql 2000，
我需要能够通过电子邮件发送详细说明许多事件的月度报告。
（我的电子邮件有点可疑）。
我需要通过电子邮件发送的数据包括有关人们参加的某些课程数量的报告。 （到目前为止很简单，几个内部联接和一个 Count() ，我就在那里。）
除此之外，我们运行的一些内部课程有一个到期日期，这会提示推荐人课程。我已经能够粗略地获取我需要的数据，方法是使用第一部分的 sql 代码并将结果集粘贴到临时表中，然后迭代该表中的每一行，获取用户 ID，查询用户课程出勤情况，按日期排序，使最早的位于顶部，并只取 TOP 1 记录。
这看起来效率很低，所以有什么方法可以修改我当前的查询，以便我也可以获得用户参加的最早课程的日期？
例如
SELECT uName, COUNT(uId), [ not sure what would go in here] FROM UserDetails
INNER JOIN PassDates
ON PassDates.fkUser = uId)
GROUP BY uName, uId
，在哪里
UserDetails
uId
uName
，
PassDates
fkUser
CourseId
PassDate
希望我已经解释得足够好，有人可以帮助我。
Using Microsoft Sql 2000
I have a requirement to be able to email a monthly report that details a number of events.
(I have got the email bit sussed).
Amongst the data I need to email is a report on the number of certain courses people have attended. (so far so easy, couple of inner joins and a Count() and Im there.)
To add to that, some of the internal courses that we run have an expiry date which prompts a referesher course. I have been able to crudely get the data I need by using the sql code for part one and sticking the result set into a temp table, then by iterating over each row in that table, getting the user Id, querying the users course attendences, sorting it on date so that the earliest is at the top, and just taking the TOP 1 record.
This seems so inefficient, so is there any way I can ammend my current query so that I can also get the date of just the earliest course that the user attended?
i.e.
SELECT uName, COUNT(uId), [ not sure what would go in here] FROM UserDetails
INNER JOIN PassDates
ON PassDates.fkUser = uId)
GROUP BY uName, uId
where, for examples sake
UserDetails
uId
uName
and
PassDates
fkUser
CourseId
PassDate
Hope Ive explained this well enough for someone to help me.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
要回答问题..
如果您的用户还没有任何课程，您可以将其转换为左连接
To put an answer to the question..
You can turn it into a left join if you have users without any courses (yet)