请高手帮忙，SQL查询的问题。

[wubaowang]

表：
OrderId customer_customerId calldate
1 2 2008-09-21
2 3 2008-02-23
3 4 2008-05-21
4 2 2008-07-21
5 2 2008-12-21
6 5 2008-11-21

请问怎样才能查询出按calldate字段排序customer_customerId字段不重复显示

[fcuandy]

这样?

select customer_customerId,max(calldate) from tb group by customer_customerid order by max(calldate)

[wzy_love_sly]

问题是怎么去重复，取大取小？

[pt1314917]

到底结果应该是怎么样的呢？

[hery2002]

use [csdn]
go
if object_id(N'tbl') is not null
begin
   drop table tbl
end
go
create table tbl(OrderId int identity(1,1),customer_customerId  int,  calldate datetime)
go
insert into tbl values (2,'2008-09-21')
insert into tbl values (3,'2008-02-23' )

insert into tbl values (4,'2008-05-21')
insert into tbl values (2,'2008-07-21')
insert into tbl values (2,'2008-12-21')
insert into tbl values (5,'2008-11-21')
go

select customer_customerId,max(calldate) as max_calldate from tbl group by customer_customerId order by customer_customerId,max(calldate)
/*
customer_customerId 
------------------- -----------------------
2                   2008-12-21 00:00:00.000
3                   2008-02-23 00:00:00.000
4                   2008-05-21 00:00:00.000
5                   2008-11-21 00:00:00.000

(4 row(s) affected)
*/
select customer_customerId,min(calldate) as min_calldate from tbl group by customer_customerId order by customer_customerId,min(calldate)
/*
customer_customerId min_calldate
------------------- -----------------------
2                   2008-07-21 00:00:00.000
3                   2008-02-23 00:00:00.000
4                   2008-05-21 00:00:00.000
5                   2008-11-21 00:00:00.000

(4 row(s) affected)
*/
go
use [csdn]
go
drop table tbl
go

[Kevin2328]

问题请描述清楚，你想的结果到底是怎样啊。

[dawugui]

[Quote=引用楼主 wubaowang 的帖子:]
表：
OrderId customer_customerId calldate
1 2 2008-09-21
2 3 2008-02-23
3 4 2008-05-21
4 2 2008-07-21
5 2 2008-12-21
6 5 2008-11-21

请问怎样才能查询出按calldate字段排序customer_customerId字段不重复显示
[/Quote]

create table tb(OrderId int, customer_customerId int, calldate varchar(10))
insert into tb values(1 , 2 , '2008-09-21') 
insert into tb values(2 , 3 , '2008-02-23') 
insert into tb values(3 , 4 , '2008-05-21') 
insert into tb values(4 , 2 , '2008-07-21') 
insert into tb values(5 , 2 , '2008-12-21') 
insert into tb values(6 , 5 , '2008-11-21')
go

--按customer_customerId分组取calldate最大的值所在行的数据。
--方法1：
select a.* from tb a where calldate = (select max(calldate) from tb where customer_customerId = a.customer_customerId) order by a.customer_customerId
--方法2：
select a.* from tb a where not exists(select 1 from tb where customer_customerId = a.customer_customerId and calldate > a.calldate)
--方法3：
select a.* from tb a,(select customer_customerId,max(calldate) calldate from tb group by customer_customerId) b where a.customer_customerId = b.customer_customerId and a.calldate = b.calldate order by a.customer_customerId
--方法4：
select a.* from tb a inner join (select customer_customerId , max(calldate) calldate from tb group by customer_customerId) b on a.customer_customerId = b.customer_customerId and a.calldate = b.calldate order by a.customer_customerId
--方法5
select a.* from tb a where 1 > (select count(*) from tb where customer_customerId = a.customer_customerId and calldate > a.calldate ) order by a.customer_customerId
/*
OrderId     customer_customerId calldate   
----------- ------------------- ---------- 
5           2                   2008-12-21
2           3                   2008-02-23
3           4                   2008-05-21
6           5                   2008-11-21

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

--按customer_customerId分组取calldate最小的值所在行的数据。
--方法1：
select a.* from tb a where calldate = (select min(calldate) from tb where customer_customerId = a.customer_customerId) order by a.customer_customerId
--方法2：
select a.* from tb a where not exists(select 1 from tb where customer_customerId = a.customer_customerId and calldate < a.calldate)
--方法3：
select a.* from tb a,(select customer_customerId,min(calldate) calldate from tb group by customer_customerId) b where a.customer_customerId = b.customer_customerId and a.calldate = b.calldate order by a.customer_customerId
--方法4：
select a.* from tb a inner join (select customer_customerId , min(calldate) calldate from tb group by customer_customerId) b on a.customer_customerId = b.customer_customerId and a.calldate = b.calldate order by a.customer_customerId
--方法5
select a.* from tb a where 1 > (select count(*) from tb where customer_customerId = a.customer_customerId and calldate < a.calldate) order by a.customer_customerId
/*
OrderId     customer_customerId calldate   
----------- ------------------- ---------- 
4           2                   2008-07-21
2           3                   2008-02-23
3           4                   2008-05-21
6           5                   2008-11-21

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

drop table tb