select distinct 的问题？

[liuhuan1024]

select distinct firstname,userid from lawyer where vn='english' and letter='d' order by userid asc
我想搜索出来的firstname不重复，但按上面的句子，出来还是重复的。
select distinct firstname from lawyer where vn='english' and letter='d'
但是这样是可以的。
但我要求搜索出来的字段不止有firstname，还要有userid,sex.......字段，要怎么改呀？
急，谢谢了

[qfacy]

userid唯一了

[raas]

select distinct(firstname) from lawyer where vn='english' and letter='d'

[liuhuan1024]

raas(朗仕) (
select distinct(firstname) from lawyer where vn='english' and letter='d'
--------------------------
这个是行，但搜索出来的表，只有firstname这一个字段了

[RainYang]

select firstname,userid from lawyer a where (select count(*) from lawyer b where a.firstname=b.firstname)>1

[RainYang]

或者select firstname,userid,sex lawyer where userid in ( select min(userid) from lawyer group by firstname having count(userid)>1)
试一下，我没测试

[liuhuan1024]

YangYuWeb(飘邈...)
还是重复显示了。

[RainYang]

不会吧，你是SQLSERVER么？

[asp2001]

用个临时表 呵呵
select distinct firstname into tmplawer from lawyer where vn='english' and letter='d'

select lawyer.firstname,lawyer.userid from lawyer,tmplawer where lawyer.firstname=tmplawyer.firstname

但感觉firstname不重复有什么意义呀? name重复的lawyer肯定很多了

[itzhiren]

要求唯一的userid

[xiaoyuehen]

select * lawyer where userid in (select min(userid) from lawyer group by firstname)
userid 为不重复的字段.

[xiaoyuehen]

漏了from

select * from lawyer where userid in (select min(userid) from lawyer group by firstname)
userid 为不重复的字段.

[songhp]

应该是你的数据结构或者资料有问题吧。如果一个FirstName对应一个UserID，很简单使用Group By就可以选择出来。但是如果FisrtName对应多个UserID的话，就必须设定选择规则。例如选择最小的UserID。选择方法如下：
Select FirstName , Min(UserID) As UserID From Lawyer Group By FirstName Order By FirstName