group by 时间出现格式问题

zhuanzhilm

test.zip (21.94 KB, 下载次数: 2)

2014-8-14 08:00 上传

点击文件名下载附件

当准备用group by 分组时间查询的时候,发现同时间段被分成不同组的问题,将原表的时间更改格式后就可以成功分组,但是原表有十几万记录,无法更改格式,求助解决办法.

amazeyeli

SELECT tbl_sheet1.Date, tbl_sheet1.Eee, format(tbl_sheet1.StartTime,"hh:mm:ss"), format(tbl_sheet1.EndTime,"hh:mm:ss")
FROM tbl_sheet1
GROUP BY tbl_sheet1.Date, tbl_sheet1.Eee, format(tbl_sheet1.StartTime,"hh:mm:ss"), format(tbl_sheet1.EndTime,"hh:mm:ss")

zhuanzhilm

amazeyeli 发表于 2014-8-14 09:29
SELECT tbl_sheet1.Date, tbl_sheet1.Eee, format(tbl_sheet1.StartTime,"hh:mm:ss"), format(tbl_sheet1.E ...

非常感谢,解决了问题,再请教一个问题,现在我想加一列计算时间差,写出来的貌似不对,不知道哪里错了  
SELECT tbl_sheet1.Date, tbl_sheet1.Eee, format(tbl_sheet1.StartTime,"hh:mm") AS StartTime, format(tbl_sheet1.EndTime,"hh:mm") AS EndTime, [EndTime]*24*60-[StartTime]*24*60 AS TimeBatchMins
FROM tbl_sheet1
GROUP BY tbl_sheet1.Date, tbl_sheet1.Eee, format(tbl_sheet1.StartTime,"hh:mm"), format(tbl_sheet1.EndTime,"hh:mm");

zhuanzhilm

我自己改了下，这回对了.
SELECT tbl_sheet1.Date, tbl_sheet1.Eee, Format(tbl_sheet1.StartTime,"Short Time") AS Start_Time, Format(tbl_sheet1.EndTime,"Short Time") AS End_Time, DateDiff ('n' ,[Start_Time],[End_Time] ) AS TimeBatchMins
FROM tbl_sheet1
GROUP BY tbl_sheet1.Date, tbl_sheet1.Eee, Format(tbl_sheet1.StartTime,"Short Time"), Format(tbl_sheet1.EndTime,"Short Time");