如何选择SQL Server中记录组的最后一条记录 [英] How to select last record of group of records in SQL server

问题描述

大家好！ 
我确信    做但我不能做那个。我的问题：
我有两个表的SQL服务器数据 base ：1-parent_table 2-child_table 
父表有 5 列 as 跟随：
 Id emp_num group_num edit_num name_emp 
 ---------- -------------------------------------- 
  1   912230   1   1  James 
  2   912230   1   2  James 
  3   912230   2   1  David 
  4   912230   2   2  David 
  5   914350   1   1  John 
  6   914350   2   1 标记
。 
。 
。 
 Id列是主键，name_emp的每条记录都有独占的emp_num和独占的 group 有很多edition_num。 父表中的每个记录是 exclosive。 
对于父表的每个记录，在子表中存在许多记录。 
 Id emp_num group_num edit_num name_emp良好的付款日期折扣
 --------------------------------- ------------------------------------------------ 
  1   912230   1   1  James egg  100  20016/01/01  10  
  2   912230   1   1  James pear  200  2016/02/01  15  
  3   914350   2  < span class =code-digit> 1  mark egg  100  2016/02/02  10  
 。 
。 
。 
我如何获取到最新记录（x  x 的x编辑）< span class =code-keyword> in 子表 每个记录 父表在日期上没有 base 但是我在插入
的最后一条记录中为例如我怎样才能选择 id =  2  在 chld表中如何显示计数行哪些折扣& gt;  0    lable.Text 
 
请帮忙！ 
谢谢

我的尝试：



i尝试此查询：



使用mydatabase

选择emp_num，group_num，edit_num，id = MAX（id）

来自tbl_child

Group By emp_num，group_num，edit_num



它使我的详细信息表（子）成功并显示最后一条记录，但当我将其他列添加到SELECT错误时只显示3列错误显示：

列'tbl_child.sh_ghab'在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句。

解决方案

SQL Server不保证记录存储的任何特定顺序（与FoxPro等平面模型相比）。如果您想要记录条目信息，您必须在表格中考虑它。如果[id]字段是 IDENTITY 字段，那么它总是按顺序排列，最新的是最大的数字。



所以 - 所有你需要做的是适当地将你的查询写入 JOIN 父表和子表，并使用 WHERE 子句（或 HAVING 子句，如果必要的）你要从你想要用来选择最新记录的表中请求 MAX（id）。



既然你没有'提供你的实际查询，你的你有什么尝试并没有告诉我们什么，我不能更具体。

Hi everybody!
	I am sure this is pretty simple to do but I can’t do that. My problem :
I Have sql server data base with two table :1-parent_table   2-child_table
Parent table has 5 columns  as follow:    
Id    emp_num   group_num  edit_num  name_emp 
------------------------------------------------      
1     912230      1           1      James              
2     912230      1           2      James              
3     912230      2           1      David               
4     912230      2           2      David               
5     914350      1           1      John  
6     914350      2           1      Mark
.
.       
.         
Id column is primary key and   each  record of name_emp  has exclusive emp_num and exclusive group and may has many edition_num . each record in parent table is exclosive.  
For each records of parent table Exist many records in child table.
Id    emp_num   group_num  edit_num  name_emp  good  payment  date       discount
---------------------------------------------------------------------------------
1     912230     1         1        James     egg    100     20016/01/01  10
2     912230     1         1        James     pear   200     2016/02/01   15
3     914350     2         1        mark      egg    100     2016/02/02   10
.
.
.
How can  I  get  to latest  record of  (emp_num of X group of x edit) in child  table  for each record in parent table not base on date but i maen last record that inserted
For e.g  how can I select  id = 2 in chld table  and how can I   show  Count rows which discount > 0  in lable.Text

Please help!
Thanks

What I have tried:

i try this query:

Use mydatabase
Select emp_num,group_num,edit_num,id=MAX(id)
From tbl_child
Group By emp_num,group_num,edit_num

it make group my detail table(child) successfully and show last record but only display 3 columns when I add other column to SELECT error show:
Column 'tbl_child.sh_ghab' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

解决方案

SQL Server does not guarantee any particular order for record storage (vs. a flat model such as FoxPro). If you want record entry information you must make allowance for it in the table. If the [id] field is an IDENTITY field then it is always in sequential order, newest with the largest number.

So - all you need to do is write your query to JOIN the parent and child tables appropriately, and the use a WHERE clause (or HAVING clause, if necessary) where you request MAX(id) from the table you wish to use to select the most recent record.

Since you didn't give your actual query, and your "What have you tried" doesn't tell us anything, I cannot be more specific.

这篇关于如何选择SQL Server中记录组的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！