sql选择详细信息表的最后一条记录 [英] sql select last record of detail table

问题描述

您好，

我在SQL 2008 Server中有2个表，主表和详细信息，详细信息表有一个日期字段，我想查看每个主表详细信息表的最后一个记录的日期字段记录。我想要将最后一条记录添加到新数据表中或将其添加到网格中，以便我可以检查date.now函数，如果每条记录的日期符合某些条件，例如。如果从date.now函数日期字段值是一个月前进：date.now = 2013年7月18日和日期字段值os 2013年8月18日然后执行某些操作，例如显示消息框。我已经为检查日期制作了代码（如果需要我会在VB .Net中发布）但是我如何进行选择呢，就像这样的工作？

Hello,
I have 2 tables in an SQL 2008 Server, master and detail, detail table has a date field, I want to check the last record's date field of the detail table of every master record. I want either to add the last record to a NEW datatable or add it to a grid so I can check with the date.now function if the date of each record meets some criteria eg. if from the date.now function date field value is a month forward : date.now = 18 July 2013 and date field value os 18 August 2013 then do something eg display a messagebox. I have made the code for the check date (if need I will post it in VB .Net) but how can I do the selection, is something like this work?

For i = 0 To datatable.Rows.Count - 1
            combobox.Items.Add(datatable.Rows(i).Item("date").ToString)
        Next

也许是dbgrid或其他东西。或者我可以在带有sqldataadapter的SQL字符串中使用内部联接，并将详细信息的表最后记录的日期字段添加到新数据集并进行检查吗？如果你可以添加一些SQL代码吗？

maybe it is a dbgrid or something. OR can I use an inner join in an SQL string with an sqldataadapter and add the detail's table last record's datefield to a NEW dataset and do the check? if you can add some SQL code to do it?

master TABLE : Student
StudentId   StudentName
1           PRASAD
2           RAM
3           RAJ




detail TABLE : Enroll
EnrollId        Course      StudentId   RegesterdDate
1               JAVA        1           2014-07-05 09:03:22.330

2               .NET        1           2014-07-07 09:03:22.330

3               HTML        2           2014-07-01 09:03:22.330

4               MVC         2           2014-07-09 09:03:22.330 value I want to select the most recent date of the detail table this is one record of student RAM

5               CSS         3           2014-07-05 09:03:22.330

6               JQUERY      3           2014-07-06 09:03:22.330




Expected Result:
StudentId   StudentName     Course      RegesterdDate
1           RAM             MVC         2014-07-09 09:03:22.330

谢谢

Thank you

推荐答案

不确定是否这就是你的意思。在我的项目中，我不得不用链接表中的数据列出最新记录。我的查询是（名称已更改），您可以添加其他所需的列：

Not sure if this what you mean. In my project I had to list the latest record with data from linked tables. My query was (with names changed), you can add other columns you need:

string query = "select StartTimestamp from Table1, Table2 ";
       query += "where Table1.ID=Table2.ID ";
       query += "and StartTimestamp in (select MAX(StartTimestamp) from Table1)";

您还可以在排序后使用SELECT TOP命令。

You could also use SELECT TOP command after sorting.

尝试此查询。

Try this query.

select a.StudentId,StudentName,Course,RegesterdDate from Student a inner join Enroll  b on a.StudentId = b.StudentId
 Inner join (
 select StudentId,MAX(RegesterdDate) rdate from Enroll
 group by StudentId) as p on p.StudentId = b.StudentId and p.rdate = b.RegesterdDate

这篇关于sql选择详细信息表的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！