if object_id('[TB]') is not null drop table [TB] go create table [TB] (id int,dd datetime) insert into [TB] select 1,'2012-05-02 09:00' union all select 1,'2012-05-02 16:00' union all select 2,'2012-05-03 08:00'select * from [TB] SELECT CONVERT(VARCHAR(10),dd,120) AS '日期',MIN(dd) AS 'min',CASE WHEN MIN(dd) = MAX(dd) THEN null ELSE MAX(dd) END AS 'max' FROM dbo.TB GROUP BY CONVERT(VARCHAR(10),dd,120),id /* 日期 min max 2012-05-02 2012-05-02 09:00:00.000 2012-05-02 16:00:00.000 2012-05-03 2012-05-03 08:00:00.000 NULL*/ ??
select min(date),max(date) from test where convert(varchar(10),date,120)='2012-05-28'
--类似这样 select xingming,isnull(max(riqi),'NULL'),isnull(min(riqi),'NULL') from kaoqinbiao group by xingming
go
create table [TB] (id int,dd datetime)
insert into [TB]
select 1,'2012-05-02 09:00' union all
select 1,'2012-05-02 16:00' union all
select 2,'2012-05-03 08:00'select * from [TB]
SELECT CONVERT(VARCHAR(10),dd,120) AS '日期',MIN(dd) AS 'min',CASE WHEN MIN(dd) = MAX(dd) THEN null ELSE MAX(dd) END AS 'max'
FROM dbo.TB
GROUP BY CONVERT(VARCHAR(10),dd,120),id
/*
日期 min max
2012-05-02 2012-05-02 09:00:00.000 2012-05-02 16:00:00.000
2012-05-03 2012-05-03 08:00:00.000 NULL*/
??
--类似这样
select xingming,isnull(max(riqi),'NULL'),isnull(min(riqi),'NULL') from kaoqinbiao group by xingming