如何得到唯一记录
xinxi
id studentid
1 1
2 2
3 1
4 1
5 2
6 3
studentstudentid name
1 张二
2 张1
3 张六
4 张大
5 张大1
6 张大2
7 张大3
8 张大4
9 张大5
我想根据xinxi表的studentid得到student表的name并且一个studentid只想显示一个name结果:id sutentid name
1 1 张二
2 2 张1
3 3 张六
xinxi
id studentid
1 1
2 2
3 1
4 1
5 2
6 3
studentstudentid name
1 张二
2 张1
3 张六
4 张大
5 张大1
6 张大2
7 张大3
8 张大4
9 张大5
我想根据xinxi表的studentid得到student表的name并且一个studentid只想显示一个name结果:id sutentid name
1 1 张二
2 2 张1
3 3 张六
--> 测试时间：2009-12-09 12:16:23
--> 测试菜鸟：l8r
--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/if object_id('[xinxi]') is not null drop table [xinxi]
create table [xinxi]([id] int,[studentid] int)
insert [xinxi]
select 1,1 union all
select 2,2 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,3
if object_id('[student]') is not null drop table [student]
create table [student]([studentid] int,[name] varchar(5))
insert [student]
select 1,'张二' union all
select 2,'张1' union all
select 3,'张六' union all
select 4,'张大' union all
select 5,'张大1' union all
select 6,'张大2' union all
select 7,'张大3' union all
select 8,'张大4' union all
select 9,'张大5'select id,A.studentid,name from xinxi A,student B where A.studentid=B.studentid and
not exists(select 1 from xinxi where A.studentid=studentid and id<A.id)
/*id studentid name
----------- ----------- -----
1 1 张二
2 2 张1
6 3 张六（所影响的行数为 3 行）*/drop table xinxi,[student]
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-12-09 12:11:14
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[xinxi]
if object_id('[xinxi]') is not null drop table [xinxi]
go
create table [xinxi]([id] int,[studentid] int)
insert [xinxi]
select 1,1 union all
select 2,2 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,3
--> 测试数据：[student]
if object_id('[student]') is not null drop table [student]
go
create table [student]([studentid] int,[name] varchar(5))
insert [student]
select 1,'张二' union all
select 2,'张1' union all
select 3,'张六' union all
select 4,'张大' union all
select 5,'张大1' union all
select 6,'张大2' union all
select 7,'张大3' union all
select 8,'张大4' union all
select 9,'张大5'
--------------开始查询--------------------------
select
a.studentid as id,b.studentid,b.name
from
[xinxi] a
join [student] b on
a.studentid=b.studentid
and
not exists (select 1 from xinxi where a.studentid=studentid and id<a.id)
----------------结果----------------------------
/* id studentid name
----------- ----------- -----
1 1 张二
2 2 张1
3 3 张六(3 行受影响)*/