比较难的SQL语句

[aquadp]

数据库是ACCESS
表名：TABLE
表结构：
－－－－－－－－－－－－
autoid （自动编号）
cdate （日期型）
eid （字符串型）/*人员编号*/
ename （字符串型）/*人员姓名*/
－－－－－－－－－－－－

实现功能：查询一个月中每天每个人的时间最早和最晚的记录
例：
autoid cdate eid ename
1 2002-02-01 11:00:00 2 xxx
2 2002-02-01 12:00:00 2 xxx
3 2002-02-01 15:33:03 2 xxx
4 2002-02-01 19:01:23 2 xxx
5 2002-02-01 10:03:55 3 yyy
6 2002-02-01 10:56:00 3 yyy
7 2002-02-01 19:55:03 3 yyy
8 2002-02-02 10:55:55 2 xxx
9 2002-02-02 17:55:55 2 xxx
10 2002-02-02 18:45:55 2 xxx
12 2002-02-02 05:15:55 3 yyy
13 2002-02-02 06:55:55 3 yyy
14 2002-02-02 14:55:55 3 yyy
.......

Query Result:
autoid cdate eid ename
1 2002-02-01 11:00:00 2 xxx
4 2002-02-01 19:01:23 2 xxx
5 2002-02-01 10:03:55 3 yyy
7 2002-02-01 19:55:03 3 yyy
8 2002-02-02 10:55:55 2 xxx
10 2002-02-02 18:45:55 2 xxx
12 2002-02-02 05:15:55 3 yyy
14 2002-02-02 14:55:55 3 yyy
......

请用SQL语句实现之

[aquadp]

非常感谢 CrazyFor(蚂蚁) :
再就是union all是什么意思，起什么作用啊？

[CrazyFor]

select min(cdate),name from table group by ename,year(cdate),month(cdate)
union all
select max(cdate),name from table group by ename,year(cdate),month(cdate)

[CrazyFor]

更新：
select min(cdate),name from table group by ename,year(cdate),month(cdate),day(cdate)
union all
select max(cdate),name from table group by ename,year(cdate),month(cdate),day(cdate)

[愉快的登山者]

select A.* from yourtable as A,
(select min(cdate) cdate, ename
from yourtable group by ename,year(cdate),month(cdate)
union all select max(cdate) , ename
from yourtable group by ename,year(cdate),month(cdate)) as B
where A.cdate = B.cdate
and A.ename = B.ename

[愉快的登山者]

select A.* from yourtable as A,
(select min(cdate) cdate, ename
from yourtable group by ename,year(cdate),month(cdate),day(cdate)
union all select max(cdate) , ename
from yourtable group by ename,year(cdate),month(cdate)),day(cdate)) as B
where A.cdate = B.cdate
and A.ename = B.ename
and year(A.cdate) = @year
and month(A.cdate) = @month

[enhydraboy]

agree CrazyFor(蚂蚁)

[Rewiah]

id保证连续：

select * from table where autoid=1
union all
select a.* from table a,table b
where b.autoid=a.autoid+1
and a.eid<>b.eid
union all
select b.* from table a,table b
where b.autoid=a.autoid+1
and a.eid<>b.eid
union all
select * from table where autoid=(select max(autoid) from table)

[Rewiah]

这个问题没有说清楚，例子不好，如果例子如下，楼主说说结果：

autoid cdate eid ename
1 2002-02-01 11:00:00 2 xxx
2 2002-02-01 12:00:00 2 xxx
3 2002-02-01 15:33:03 2 xxx
4 2002-02-01 19:01:23 2 xxx
5 2002-02-01 10:03:55 3 yyy
6 2002-02-02 10:55:55 2 xxx
7 2002-02-02 05:15:55 3 yyy
8 2002-02-02 06:55:55 3 yyy
9 2002-02-02 14:55:55 3 yyy
10 2002-02-02 11:00:00 2 xxx
11 2002-02-02 12:00:00 2 xxx
12 2002-02-02 15:33:03 2 xxx


两个问题：
1、同一个eid 一次只出现一条
2、同一个eid 一天出现两次

[19191919]

declare @月份 int
set @月份=2
select #t.autoid,#t.cdate,eid,#t.ename from #t,
(select day(cdate) as d,ename,min(cdate) as cdate from #t
where month(cdate)=@月份
group by day(cdate),ename
union
select day(cdate),ename,max(cdate) as cdate from #t
where month(cdate)=@月份
group by day(cdate),ename
) as a
where #t.cdate=a.cdate and #t.ename=a.ename

[huxin1012]

union all:把两次查询的结果（或表）合并到一起（纵向合并）。