select min(empid),departid from tableName group by departid
select min(empid),departid from tableName group by departid
create table ai (empid int, departid int) insert into ai select 1,1 insert into ai select 2,1 insert into ai select 3,1 insert into ai select 4,2 insert into ai select 5,2 insert into ai select 6,2goselect * from ai as a where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)
create table ai (empid int, departid int) insert into ai select 1,1 insert into ai select 2,1 insert into ai select 3,1 insert into ai select 4,2 insert into ai select 5,2 insert into ai select 6,2goselect * from ai as a where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)goselect min(empid)as empid,departid from ai group by departid
select min(empid),departid from tableName group by departid 我上面,只是一个例子,其实字段很多,当min,max不能处理的那些字段怎么办
create table ai (empid int, departid int) insert into ai select 1,1 insert into ai select 2,1 insert into ai select 3,1 insert into ai select 4,2 insert into ai select 5,2 insert into ai select 6,2goselect * from ai as a where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)这个方法啦 字段再多也没有关系
select min(empid),departid from tableName group by departid
用aierong(皑婀瑢-数据库XML.NET联盟会科长)的
一个相关子查询的应用而已..建议去翻翻这个版的精华区或者faq
上面aierong(皑婀瑢-数据库XML.NET联盟会科长) 写的是针对有单一主键定义的情况.如果没有单一主键,可以通过类型转换及字符串操作虚拟一个符合主键完成关联系条件..举例如下: create table ai (departid int,text1 varchar,text2 varchar) insert into ai select 1,'t','a' insert into ai select 1,'t','b' insert into ai select 1,'a','c' insert into ai select 3,'h','d' insert into ai select 4,'d','d' insert into ai select 6,'j','f' insert into ai select 3,'d','f' insert into ai select 4,'h','e' insert into ai select 6,'h','h'select * from ai as a where (a.text1+a.text2)=(select top 1 (b.text1+b.text2) from ai as b where a.departid=b.departid)
假如你有三个字段a,b,c 那么这样写 select min(a),b,c from tableName group by b,c
select * from ai as a where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid group by departid)
对 yonghengdizhen(9.18=>警钟长鸣,勿忘国耻)的做一下修改,我想应是你想要的了!create table ai (departid int,text1 varchar,text2 varchar) insert into ai select 1,'t','a' insert into ai select 1,'t','a' insert into ai select 1,'t','b' insert into ai select 1,'a','c' insert into ai select 3,'h','d' insert into ai select 4,'d','d' insert into ai select 6,'j','f' insert into ai select 3,'d','f' insert into ai select 4,'h','e' insert into ai select 6,'h','h'select distinct * from ai as a where (str(a.departid)+a.text1+a.text2)=(select top 1 (str(b.departid)+b.text1+b.text2) from ai as b where a.departid=b.departid)
where id in(select top 1 id from tablename as b where a.id=b.id)
group by id看看吧!
empid departid
1 1
2 1
3 1
4 2
5 2
6 2
我只想取
1 ,1
4,2
怎么取
insert into ai select 1,1
insert into ai select 2,1
insert into ai select 3,1
insert into ai select 4,2
insert into ai select 5,2
insert into ai select 6,2goselect *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)
insert into ai select 1,1
insert into ai select 2,1
insert into ai select 3,1
insert into ai select 4,2
insert into ai select 5,2
insert into ai select 6,2goselect *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)goselect min(empid)as empid,departid
from ai
group by departid
我上面,只是一个例子,其实字段很多,当min,max不能处理的那些字段怎么办
insert into ai select 1,1
insert into ai select 2,1
insert into ai select 3,1
insert into ai select 4,2
insert into ai select 5,2
insert into ai select 6,2goselect *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)这个方法啦
字段再多也没有关系
写的是针对有单一主键定义的情况.如果没有单一主键,可以通过类型转换及字符串操作虚拟一个符合主键完成关联系条件..举例如下:
create table ai (departid int,text1 varchar,text2 varchar)
insert into ai select 1,'t','a'
insert into ai select 1,'t','b'
insert into ai select 1,'a','c'
insert into ai select 3,'h','d'
insert into ai select 4,'d','d'
insert into ai select 6,'j','f'
insert into ai select 3,'d','f'
insert into ai select 4,'h','e'
insert into ai select 6,'h','h'select *
from ai as a
where (a.text1+a.text2)=(select top 1 (b.text1+b.text2) from ai as b where a.departid=b.departid)
那么这样写
select min(a),b,c from tableName group by b,c
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid group by departid)
insert into ai select 1,'t','a'
insert into ai select 1,'t','a'
insert into ai select 1,'t','b'
insert into ai select 1,'a','c'
insert into ai select 3,'h','d'
insert into ai select 4,'d','d'
insert into ai select 6,'j','f'
insert into ai select 3,'d','f'
insert into ai select 4,'h','e'
insert into ai select 6,'h','h'select distinct *
from ai as a
where (str(a.departid)+a.text1+a.text2)=(select top 1 (str(b.departid)+b.text1+b.text2) from ai as b where a.departid=b.departid)