如何从ms访问中的一组记录中只选择一条记录？ [英] How to Select only one record from group of records in ms access?

问题描述

我有一个名为datafile的表，其中包含以下记录：



FamilyCode名称年龄

1 ABC 23

1 DEF 25

2 GHI 58

2 JKL 23

2 PQR 56


和另一个包含以下记录的SCHEMETRN表：



FamilyCode SchemeId

1 2

2 10



现在我写了一个查询为

SELECT distinct d.FamilyCode，first d.FullName，d.Age from datafile d内连接SCHEMETRN2 s2在d.FamilyCode = s2.FamilyCode



这给我的结果为



FamilyCode姓名年龄

1 ABC 23

1 DEF 25

2 GHI 58

2 JKL 23

2 PQR 56



我只想要一个家庭代码1的记录和一个家庭代码2的记录..
如何获得这个？

解决方案

 选择 B.FamilyCode，B.Name，B.age 来自数据文件B，
（ SELECT  max（age）age，FamilyCode  FROM  datafile A  WHERE  A.FamilyCode  IN （ SELECT  FamilyCode  FROM  SCHEMETRN）
  group   by  FamilyCode）C 
 其中 B. FamilyCode = C.FamilyCode 和 B.age = C.age 

i在我的sqlserver中得到它，而SCHEMETRN2是SCHEMETRN！



结果是：



1 DEF 25

2 GHI 58

你需要修改SQL代码和你将得到预期的答案



原始查询

SELECT distinct d.FamilyCode，first d.FullName，d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode



修改后的查询

  SELECT   distinct  d.FamilyCode，首先是d.FullName，d.Age 来自 SCHEMETRN2 s2 内部 加入数据文件d   s2.FamilyCode = d.FamilyCode 

你需要修改SQL代码，你会得到预期的答案



原始查询

  SELECT   distinct  d.FamilyCode，第一个d.FullName，d.Age 来自数据文件d 内部  join  SCHEMETRN2 s2   d.FamilyCode = s2.FamilyCode 

修改后的查询

  SELECT   distinct  d.FamilyCode，首先是d.FullName，d.Age 来自 SCHEMETRN2 s2  inner   s2.FamilyCode = d.FamilyCode 

< join datafile d / p>

I have a table named datafile that contains records as below:

FamilyCode Name Age
1 ABC 23
1 DEF 25
2 GHI 58
2 JKL 23
2 PQR 56

And another table SCHEMETRN that contains records as below:

FamilyCode SchemeId
1 2
2 10

Now I have written query as
"SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode "

This gives me result as

FamilyCode Name Age
1 ABC 23
1 DEF 25
2 GHI 58
2 JKL 23
2 PQR 56

I only want one record of familycode 1 and one record of familycode 2..
How to get this?

解决方案

select B.FamilyCode,B.Name,B.age from datafile B,
(SELECT max(age) age,FamilyCode FROM datafile A WHERE A.FamilyCode IN(SELECT FamilyCode  FROM SCHEMETRN )
group by FamilyCode) C
where B.FamilyCode=C.FamilyCode and B.age=C.age

i got it in my sqlserver,and SCHEMETRN2 is SCHEMETRN !

the result is :

1 DEF 25
2 GHI 58

You need to modify SQL code and you will get the expected answer

Original Query
SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode

Modified Query

SELECT distinct d.FamilyCode,first d.FullName,d.Age from SCHEMETRN2 s2 inner join datafile d on s2.FamilyCode = d.FamilyCode 

You need to modify SQL code and you will get the expected answer

Original Query

SELECT distinct d.FamilyCode,first d.FullName,d.Age from datafile d inner join SCHEMETRN2 s2 on d.FamilyCode = s2.FamilyCode

Modified Query

SELECT distinct d.FamilyCode,first d.FullName,d.Age from SCHEMETRN2 s2 inner join datafile d on s2.FamilyCode = d.FamilyCode 

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