怎样写这个sql语句？

[keiga]

NO NAME ...
001 WANG ...
002 YANG ...
001 ZHAO ...
002 ZHANG ...
003 LI ...

NO中有两个001和两个002,但只取一条记录，执行后:

NO NAME ...
001 WANG ...
002 YANG ...
003 LI ...

[ywqzxj]

select * from table1 where [no] in (select distinct no from table1)

[hailong525]

按NO字段分组也可以，即...GROUP BY NO!

[sdfh]

用一条语句写没那么简单。
要是只取重复记录中rowid最小的一条可以这样写（暂不考虑效率）：
select * from tablename where rowid in (select min(rowid) from tablename where no in (select no from tablename group by no having count(*)>1) group by no
union select rowid from tablename where no in (select no from tablename group by no having count(*)=1))

要是在n条重复记录中取任意的一条则要加上另一个判断条件。

考虑到效率问题，没必要一条语句完成。

[signboy]

今天的高高手都哪里去了,这些问题我都能轮到我回答
select * from tablename where pk=(select min(pk) from tablename group by no)
其中的pk为该表的关键字,最好为id,对于本表来说,如果no,和name能唯一确定一行数据,则pk=name
实在不行,把数据插入一个带自动增加的identity列中,则pk=id

[signboy]

高错,
select * from tablename a, (select min(pk) from tablename group by no) b where a.pk=b.pk

[hailong525]

select * from table1 where no in (select distinct no from table1)
完全可以满足的！

[sdfh]

修正，应为下面这样写更简单：
select * from table1 where rowid in (select min(rowid) from table1 group by no)

[chenhw]

select no,min(name) group by no

[hailong525]

再试试这个：
select * from table1 where no in (select NO，min(Name) from table1 group by NO)

[keiga]

这么多人回复:高兴
没有一个好用:失望
期待...........

[keiga]

补充:不一定要一条语句完成

[hailong525]

select NO,min(Name),min(...) from table group by NO

[Stingray]

为什么"select NO,min(Name),min(...) from table group by NO"不行,是不是你对显示的那一个纪录还有其他要求

[hook_cjh]

select no,min(name) from table group by no

[keiga]

to Stingray()
为什么只显示一个纪录

[keiga]

NO NAME
001 WANG
001 YANG
001 ZHAO
002 ZHANG
002 LI
003 Shun
003 Zhou

执行后:显示3个纪录

NO NAME
001 WANG
002 LI
003 Shun

[N_chow]

你這個表里沒有關鍵字了嗎?
要不然要取得你想要的結果非要用Cursor不可。

[foolishchao]

在MsSql中
select name,no,qty,identity(int,1,1) as id into #T1 from table1
select min(id) as id into #t2 from #t1 group by no
select t1.name,t1.no,t1.qty from #t2 t2 join #t1 t1 on t2.id=t1.id
在Oracle中
select * from table1 where rowid in (select min(rowid) from table1 group by no)

[feng_zi]

显示最前一个，语句如下：
select * from table_name where rowid in(select min(rowid) from table_name group by no);
最后一个,将min改为max即可！

[yzyun]

这样就可以了：
假设表名是T_AAA：
SELECT * FROM T_AAA
WHERE NAME = (SELECT MIN(NAME) FROM T_AAA BB
WHERE T_AAA.NO=BB.NO)

用到表的别名。
绝对没有问题！