怎么用一条语句把我group by id的各个id的第一条一起取出来，就是对应的各个id就取一条

[smiletosky]

！！！！

[aierong]

from tablename as a
where id in(select top 1 id from tablename as b where a.id=b.id)
group by id

[aierong]

最好把数据帖出来
看看

[wzyu]

from tablename as a
where id in(select top 1 id from tablename as b where a.id=b.id)
group by id

看看吧！

[smiletosky]

我好象问错拉
empid departid
1 1
2 1
3 1
4 2
5 2
6 2
我只想取
1 ，1
4，2
怎么取

[cloudchen]

select min(empid),departid from tableName group by departid

[yujohny]

select min(empid),departid from tableName group by departid

[aierong]

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,2

go

select *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)

[aierong]

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,2

go

select *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)

go

select min(empid)as empid,departid
from ai
group by departid

[smiletosky]

select min(empid),departid from tableName group by departid
我上面，只是一个例子，其实字段很多，当min，max不能处理的那些字段怎么办

[smiletosky]

说明一下，一条语句！

[aierong]

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,2

go

select *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid)

这个方法啦
字段再多也没有关系

[accounant]

select min(empid),departid from tableName group by departid

[zhbname]

用aierong(皑婀瑢-数据库XML.NET联盟会科长)的

[smiletosky]

谢谢大家，我去试验一下

[happy_0325]

select *
from ai as a
where a.empid=(select min(b.empid) from ai as b where a.departid=b.departid group by departid)

[luominggui]

对 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)

[yonghengdizhen]

一个相关子查询的应用而已..建议去翻翻这个版的精华区或者faq

[yonghengdizhen]

上面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)

[yonghengdizhen]

上面的代码并没有用到类型转换.类型转换只有在复合主键由不同类型的字段构成时需要进行,这个时候将各字段转换成统一类型进行组合运算(注意,不能有损数据精度)并仍然采用上面的相关子查询方法

[jiangchuandong]

假如你有三个字段a，b，c
那么这样写
select min(a),b,c from tableName group by b,c