一个看似简单其实满郁闷的SQL问题!!!!散分了!!!

[chenjerry]

兄弟们给这个问题一个答案，不胜感激!!!!
某表数据结构如下:
ID ClassID Name Address Remarks
1 0 eafa ad
2 0 sdf d
3 1 sdfsd ad
4 1 ss ad
5 1 fsd ad
7 3 dd ad
8 3 ss sss
9 3 sd 上海
10 5 ew
11 5 jg 北京
12 6 s
13 6 gh

问题:
要求用一个SQL语句找出第二列每个相同的ClassID唯一的一条纪录(所有字段都列出来)?????
(要求是相同的ClassID只取一条记录,任何一条都均可)

[SQL77]

SELECT * FROM TB WHERE NOT EXISTS(SELECT 1 FROM TB T WHERE T.CLASSID=TB.CLASSID AND TB.ID>T.ID)

[百年树人]

select
  *
from
  tb t
where
  not exists(select 1 from tb where classid=t.classid and id<t.id)

[nalnait]

declare @m table(ID int,ClassID int,Name varchar(10),Address varchar(10),Remarks varchar(10))
insert into @m(id,classid,name,address)
select 1,0,'eafa','ad' union all
select 2,0,'sdf','d' union all
select 3,1,'sdfsd','ad' union all
select 4,1,'ss','ad' union all
select 5,1,'fsd','ad' union all
select 7,3,'dd','ad' union all
select 8,3,'ss','sss' union all
select 9,3,'sd','上海' union all
select 10,5,'ew',null union all
select 11,5,'jg','北京' union all
select 12,6,'s',null

select * from
(
select row_number()over(partition by classid order by id)xx,* from @M
) a where xx=1

/*
xx ID ClassID Name Address Remarks
-------------------- ----------- ----------- ---------- ---------- ----------
1 1 0 eafa ad NULL
1 3 1 sdfsd ad NULL
1 7 3 dd ad NULL
1 10 5 ew NULL NULL
1 12 6 s NULL NULL

(5 行受影响)
*/

[nalnait]

xx                   ID          ClassID     Name       Address    Remarks
-------------------- ----------- ----------- ---------- ---------- ----------
1                    1           0           eafa       ad         NULL
1                    3           1           sdfsd      ad         NULL
1                    7           3           dd         ad         NULL
1                    10          5           ew         NULL       NULL
1                    12          6           s          NULL       NULL

(5 行受影响)

[nalnait]

declare @m table(ID int,ClassID int,Name varchar(10),Address varchar(10),Remarks varchar(10))
insert into @m(id,classid,name,address)
select 1,0,'eafa','ad' union all
select 2,0,'sdf','d' union all
select 3,1,'sdfsd','ad' union all
select 4,1,'ss','ad' union all
select 5,1,'fsd','ad' union all 
select 7,3,'dd','ad' union all
select 8,3,'ss','sss' union all
select 9,3,'sd','上海' union all
select 10,5,'ew',null union all
select 11,5,'jg','北京' union all
select 12,6,'s',null 

select * from 
(
select row_number()over(partition by classid order by id)xx,* from @M
) a where xx=1

/*
xx                   ID          ClassID     Name       Address    Remarks
-------------------- ----------- ----------- ---------- ---------- ----------
1                    1           0           eafa       ad         NULL
1                    3           1           sdfsd      ad         NULL
1                    7           3           dd         ad         NULL
1                    10          5           ew         NULL       NULL
1                    12          6           s          NULL       NULL

(5 行受影响)

*/

[ks_reny]

SELECT * FROM tb  a WHERE NOT EXISTS(SELECT 1 FROM TB  WHERE CLASSID=a.CLASSID AND id<a.id)

[chenjerry]

谢谢各位,学习了!

[boywangliang]

select top 1 * from product where ClassID=0
有那么难吗 取第一条
不知道是不是LZ要的

[boywangliang]

看错题了

[ChinaJiaBing]

我来一句

select * from @tb a where ID= select  min(id) from @tb where classid=a.classid

[dj3688]

create  table test(id int,classid int,name varchar(10),address varchar(10),remarks varchar(10)) 
insert into test(id,classid,name,address) 
select 1,0,'eafa','ad' union all 
select 2,0,'sdf','d' union all 
select 3,1,'sdfsd','ad' union all 
select 4,1,'ss','ad' union all 
select 5,1,'fsd','ad' union all 
select 7,3,'dd','ad' union all 
select 8,3,'ss','sss' union all 
select 9,3,'sd','上海' union all 
select 10,5,'ew',null union all 
select 11,5,'jg','北京' union all 
select 12,6,'s',null 

select * from test a where id in (select  min(id) from test where classid=a.classid)
/*
id          classid     name       address    remarks    
----------- ----------- ---------- ---------- ---------- 
1           0           eafa       ad         NULL
3           1           sdfsd      ad         NULL
7           3           dd         ad         NULL
10          5           ew         NULL       NULL
12          6           s          NULL       NULL

（所影响的行数为 5 行）
*/

[JustSoSoAtCn]

老问题了，很早以前就有人问过一样的题目

[--小F--]

--给你一堆去研究下
--处理表重复记录(查询和删除)
/******************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go


--I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID<a.ID)

方法2:
select a.* from #T a join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID

方法3:
select * from #T a where ID=(select min(ID) from #T where Name=a.Name)

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID>=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID)

方法8:
select * from #T a where ID!>all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select min(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1

生成结果:
/*
ID          Name Memo
----------- ---- ----
1           A    A1
4           B    B1

(2 行受影响)
*/


--II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID)

方法2:
select a.* from #T a join (select max(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID order by ID

方法3:
select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID

方法4:
select a.* from #T a join #T b on a.Name=b.Name and a.ID<=b.ID group by a.ID,a.Name,a.Memo having count(1)=1 

方法5:
select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name)

方法6:
select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0

方法7:
select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

方法8:
select * from #T a where ID!<all(select ID from #T where Name=a.Name)

方法9(注:ID为唯一时可用):
select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

方法10:
select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

方法11:
select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

[fuhaojie626]

[Quote=引用 1 楼 SQL77 的回复:]
SQL codeSELECT * FROM TB WHERE NOT EXISTS(SELECT 1 FROM TB T WHERE T.CLASSID=TB.CLASSID AND TB.ID>T.ID)
[/Quote]
这个简单 也容易理解 顶顶