数字 日期/时间 日期/时间
ChNum StartDate FinishDate
1 2009-02-20 09:44:06 2009-02-20 09:45:14
1 2009-02-20 09:46:20 2009-02-20 09:47:18
1 2009-02-21 11:01:47 2009-02-21 11:01:55
1 2009-02-21 11:26:20 2009-02-21 11:53:25
1 2009-02-21 14:22:46 2009-02-21 14:22:48
2 2009-02-20 16:40:32 2009-02-20 16:40:32
2 2009-02-21 16:44:39 2009-02-21 16:44:39
2 2009-02-21 17:12:47 2009-02-21 17:13:45
2 2009-02-21 12:57:04 2009-02-21 12:57:04
3 2009-02-21 13:52:12 2009-02-21 13:52:12
3 2009-02-21 13:55:11 2009-02-21 13:55:11
3 2009-02-23 13:56:41 2009-02-23 13:59:33Access表里面有上面三个字段,现在有如下需求:
统计出每一天min(StartDate)及其对应的FinishDate,max(StartDate)及其对应的FinishDate,且包括记录的次数,并且group by(ChNum)
请问Sql语句应该怎么写啊?
期望的结果如下:
ChNum 次数 日期 min_StartDate min_FinishDate max_StartDate max_FinishDate
1 2 2009-02-20 09:44:06 09:45:14 09:46:20 09:47:18
1 3 2009-02-21 11:01:47 11:01:55 14:22:46 14:22:48
2 1 2009-02-20 16:40:32 16:40:32 16:40:32 16:40:32
2 3 2009-02-21 16:44:39 16:44:39 12:57:04 12:57:04
3 2 2009-02-21 13:52:12 13:52:12 13:55:11 13:55:11
3 1 2009-02-21 13:56:41 13:59:33 13:56:41 13:59:33
表达能力有限,不知道各位大虾能不能看懂啊
ChNum StartDate FinishDate
1 2009-02-20 09:44:06 2009-02-20 09:45:14
1 2009-02-20 09:46:20 2009-02-20 09:47:18
1 2009-02-21 11:01:47 2009-02-21 11:01:55
1 2009-02-21 11:26:20 2009-02-21 11:53:25
1 2009-02-21 14:22:46 2009-02-21 14:22:48
2 2009-02-20 16:40:32 2009-02-20 16:40:32
2 2009-02-21 16:44:39 2009-02-21 16:44:39
2 2009-02-21 17:12:47 2009-02-21 17:13:45
2 2009-02-21 12:57:04 2009-02-21 12:57:04
3 2009-02-21 13:52:12 2009-02-21 13:52:12
3 2009-02-21 13:55:11 2009-02-21 13:55:11
3 2009-02-23 13:56:41 2009-02-23 13:59:33Access表里面有上面三个字段,现在有如下需求:
统计出每一天min(StartDate)及其对应的FinishDate,max(StartDate)及其对应的FinishDate,且包括记录的次数,并且group by(ChNum)
请问Sql语句应该怎么写啊?
期望的结果如下:
ChNum 次数 日期 min_StartDate min_FinishDate max_StartDate max_FinishDate
1 2 2009-02-20 09:44:06 09:45:14 09:46:20 09:47:18
1 3 2009-02-21 11:01:47 11:01:55 14:22:46 14:22:48
2 1 2009-02-20 16:40:32 16:40:32 16:40:32 16:40:32
2 3 2009-02-21 16:44:39 16:44:39 12:57:04 12:57:04
3 2 2009-02-21 13:52:12 13:52:12 13:55:11 13:55:11
3 1 2009-02-21 13:56:41 13:59:33 13:56:41 13:59:33
表达能力有限,不知道各位大虾能不能看懂啊
ChNum StartDate FinishDate
1 2009-02-20 09:44:06 2009-02-20 09:45:14
1 2009-02-20 09:46:20 2009-02-20 09:47:18
1 2009-02-21 11:01:47 2009-02-21 11:01:55
1 2009-02-21 11:26:20 2009-02-21 11:53:25
1 2009-02-21 14:22:46 2009-02-21 14:22:48
2 2009-02-20 16:40:32 2009-02-20 16:40:32
2 2009-02-21 16:44:39 2009-02-21 16:44:39
2 2009-02-21 17:12:47 2009-02-21 17:13:45
2 2009-02-21 12:57:04 2009-02-21 12:57:04
3 2009-02-21 13:52:12 2009-02-21 13:52:12
3 2009-02-21 13:55:11 2009-02-21 13:55:11
3 2009-02-23 13:56:41 2009-02-23 13:59:33Access表里面有上面三个字段,现在有如下需求:
统计出每一天min(StartDate)及其对应的FinishDate,max(StartDate)及其对应的FinishDate,且包括记录的次数,并且group by(ChNum)
请问Sql语句应该怎么写啊?
期望的结果如下:
ChNum 次数 日期 min_StartDate min_FinishDate max_StartDate max_FinishDate
1 2 2009-02-20 09:44:06 09:45:14 09:46:20 09:47:18
1 3 2009-02-21 11:01:47 11:01:55 14:22:46 14:22:48
2 1 2009-02-20 16:40:32 16:40:32 16:40:32 16:40:32
2 3 2009-02-21 16:44:39 16:44:39 12:57:04 12:57:04
3 2 2009-02-21 13:52:12 13:52:12 13:55:11 13:55:11
3 1 2009-02-21 13:56:41 13:59:33 13:56:41 13:59:33
表达能力有限,不知道各位大虾能不能看懂啊
格式不好,重新编辑一下
select
ChNum,
count(*) as 次数,
convert(varchar(100),StartDate,23) as 日期,
convert(varchar(100),min(StartDate),24) as min_StartDate,
convert(varchar(100),min(FinishDate),24) as min_FinishDate,
convert(varchar(100),max(StartDate),24) as max_StartDate,
convert(varchar(100),max(FinishDate),24) as max_FinishDate
from 表名
group by ChNum,convert(varchar(100),StartDate,23)
declare @s table (ChNum int,StartDate datetime,FinishDate datetime)
insert into @s
select 1,'2009-02-20 09:44:06','2009-02-20 09:45:14' union all
select 1,'2009-02-20 09:46:20','2009-02-20 09:47:18' union all
select 1,'2009-02-21 11:01:47','2009-02-21 11:01:55' union all
select 1,'2009-02-21 11:26:20','2009-02-21 11:53:25' union all
select 1,'2009-02-21 14:22:46','2009-02-21 14:22:48' union all
select 2,'2009-02-20 16:40:32','2009-02-20 16:40:32' union all
select 2,'2009-02-21 16:44:39','2009-02-21 16:44:39' union all
select 2,'2009-02-21 17:12:47','2009-02-21 17:13:45' union all
select 2,'2009-02-21 12:57:04','2009-02-21 12:57:04' union all
select 3,'2009-02-21 13:52:12','2009-02-21 13:52:12' union all
select 3,'2009-02-21 13:55:11','2009-02-21 13:55:11' union all
select 3,'2009-02-23 13:56:41','2009-02-23 13:59:33'
select chnum,次数=count(1),日期=convert(varchar(10),startdate,120),
min_startdate=(select convert(varchar(10),min(StartDate),108) from @s where chnum=a.chnum and datediff(dd,StartDate,convert(varchar(10),a.startdate,120))=0),
min_FinishDate=(select convert(varchar(10),min(FinishDate),108) from @s where chnum=a.chnum and datediff(dd,StartDate,convert(varchar(10),a.startdate,120))=0),
max_StartDate=(select convert(varchar(10),max(StartDate),108) from @s where chnum=a.chnum and datediff(dd,StartDate,convert(varchar(10),a.startdate,120))=0),
max_FinishDate=(select convert(varchar(10),max(FinishDate),108) from @s where chnum=a.chnum and datediff(dd,StartDate,convert(varchar(10),a.startdate,120))=0)
from @s a group by chnum,convert(varchar(10),startdate,120)
order by chnum
-->刚才为了抢个摊子,太多次没抢到了
declare @t table(ChNum int,StartDate datetime,FinishDate datetime)
insert @t select 1,'2009-02-20 09:44:06','2009-02-20 09:45:14'
union all select 1,'2009-02-20 09:46:20','2009-02-20 09:47:18'
union all select 1,'2009-02-21 11:01:47','2009-02-21 11:01:55'
union all select 1,'2009-02-21 11:26:20','2009-02-21 11:53:25'
union all select 1,'2009-02-21 14:22:46','2009-02-21 14:22:48'
union all select 2,'2009-02-20 16:40:32','2009-02-20 16:40:32'
union all select 2,'2009-02-21 16:44:39','2009-02-21 16:44:39'
union all select 2,'2009-02-21 17:12:47','2009-02-21 17:13:45'
union all select 2,'2009-02-21 12:57:04','2009-02-21 12:57:04'
union all select 3,'2009-02-21 13:52:12','2009-02-21 13:52:12'
union all select 3,'2009-02-21 13:55:11','2009-02-21 13:55:11'
union all select 3,'2009-02-23 13:56:41','2009-02-23 13:59:33'
select
ChNum,
count(*) as 次数,
convert(varchar(100),StartDate,23) as 日期,
convert(varchar(100),min(StartDate),24) as min_StartDate,
convert(varchar(100),min(FinishDate),24) as min_FinishDate,
convert(varchar(100),max(StartDate),24) as max_StartDate,
convert(varchar(100),max(FinishDate),24) as max_FinishDate
from @t
group by ChNum,convert(varchar(100),StartDate,23)
order by ChNum
/*---------------------------
ChNum 次数 日期 min_StartDate min_FinishDate max_StartDate max_FinishDate
------ --- ----------- -------------- -------------- -------------- --------------
1 2 2009-02-20 09:44:06 09:45:14 09:46:20 09:47:18
1 3 2009-02-21 11:01:47 11:01:55 14:22:46 14:22:48
2 1 2009-02-20 16:40:32 16:40:32 16:40:32 16:40:32
2 3 2009-02-21 16:44:39 16:44:39 12:57:04 12:57:04
3 2 2009-02-21 13:52:12 13:52:12 13:55:11 13:55:11
3 1 2009-02-21 13:56:41 13:59:33 13:56:41 13:59:33(所影响的行数为 6 行)
-------------------------------*/
declare @t table(ChNum int,StartDate datetime,FinishDate datetime)
insert @t select 1,'2009-02-20 09:44:06','2009-02-20 09:45:14'
union all select 1,'2009-02-20 09:46:20','2009-02-20 09:47:18'
union all select 1,'2009-02-21 11:01:47','2009-02-21 11:01:55'
union all select 1,'2009-02-21 11:26:20','2009-02-21 11:53:25'
union all select 1,'2009-02-21 14:22:46','2009-02-21 14:22:48'
union all select 2,'2009-02-20 16:40:32','2009-02-20 16:40:32'
union all select 2,'2009-02-21 16:44:39','2009-02-21 16:44:39'
union all select 2,'2009-02-21 17:12:47','2009-02-21 17:13:45'
union all select 2,'2009-02-21 12:57:04','2009-02-21 12:57:04'
union all select 3,'2009-02-21 13:52:12','2009-02-21 13:52:12'
union all select 3,'2009-02-21 13:55:11','2009-02-21 13:55:11'
union all select 3,'2009-02-23 13:56:41','2009-02-23 13:59:33'
select
ChNum,
count(*) as 次数,
convert(varchar(100),StartDate,23) as 日期,
convert(varchar(100),min(StartDate),24) as min_StartDate,
convert(varchar(100),min(FinishDate),24) as min_FinishDate,
convert(varchar(100),max(StartDate),24) as max_StartDate,
convert(varchar(100),max(FinishDate),24) as max_FinishDate
from @t
group by ChNum,convert(varchar(100),StartDate,23)
order by ChNum-->以下是显示的正确的结果,楼主给的结果似乎不大对
/*
ChNum 次数 日期 min_StartDate min_FinishDate max_StartDate max_FinishDate
------ --- ----------- -------------- -------------- -------------- --------------
1 2 2009-02-20 09:44:06 09:45:14 09:46:20 09:47:18
1 3 2009-02-21 11:01:47 11:01:55 14:22:46 14:22:48
2 1 2009-02-20 16:40:32 16:40:32 16:40:32 16:40:32
2 3 2009-02-21 12:57:04 12:57:04 17:12:47 17:13:45
3 2 2009-02-21 13:52:12 13:52:12 13:55:11 13:55:11
3 1 2009-02-23 13:56:41 13:59:33 13:56:41 13:59:33
*/