SQL Query Group By Datetime 问题? [英] SQL Query Group By Datetime problem?

问题描述

我有这个 SQL 查询:

I have this SQL query:

  SELECT DISTINCT 
         [BatchCode]
         ,SUM([Quantity]) as 'Created'
         ,[TotalQuantity]
         ,[Status]
         ,[Destination]
         ,[DateCreated]
         ,[CreatedBy]
    FROM [FGIS].[dbo].[DropshipPackinglist]
GROUP BY BatchCode, TotalQuantity, Status, Destination, CreatedBy, ModifiedBy, DateCreated

结果如下:

BatchCode               Created   TotalQuantity   Status     Destination        DateCreated               CreatedBy
---------------------------------------------------------------------------------------------------------------
0005041007100AHWA11HG   86        86              CREATED    MediTelecom S.A.   2010-09-10  00:00:00.000    NULL
0005041007100AHWA11HGK  19        50              ALLOCATED  USA                2010-09-12 07:35:17.000     jy
0005041007100AHWA11HGK  31        50              ALLOCATED  USA                2010-09-12 07:35:20.000     jy

<小时>

我现在的问题是我不能对 DateCreated 进行分组，因为它有不同的时间.

---

My Problem now is I can't Group DateCreated because of it has different time .

我只想按日期对其进行分组.示例:2010-09-12

I want to group it by date only. Example: 2010-09-12

谢谢和问候...

推荐答案

我想这值得单独发布:

使用字符转换来截断日期的时间(转换或转换为 varchar)比使用 DateAdd(Day, DateDiff(Day, 0, DateCreated), 0) 慢.我解决了 支持此断言的完整脚本和性能测试结果.

Using char conversions to chop the time off dates (cast or convert to varchar) is slower than using DateAdd(Day, DateDiff(Day, 0, DateCreated), 0). I worked up full script and performance testing results to support this assertion.

SELECT DISTINCT 
   BatchCode
   ,SUM(Quantity) as Created
   ,TotalQuantity
   ,Status
   ,Destination
   ,DateAdd(Day, DateDiff(Day, 0, DateCreated), 0) as DayCreated
   ,CreatedBy
FROM FGIS.dbo.DropshipPackinglist
GROUP BY
   BatchCode,
   TotalQuantity,
   Status,
   Destination,
   CreatedBy,
   ModifiedBy,
   DateDiff(Day, 0, DateCreated) -- note that the DateAdd convert back to datetime is not needed

另外，请注意您的 GROUP BY 列表与您的 SELECT 列表不同，因此需要进行一些调整.

Also, please note that your GROUP BY list is not the same as your SELECT list so some tweaking is needed.

更新

使用 DateAdd 与 varchar 转换相比，使用 DateAdd 与 varchar 转换的 CPU 节省似乎相对较多，但绝对不是很多(每行仅几分之一毫秒).然而，这仍然是一个性能差异，在我看来最好是尽可能地保存.

It seems that the CPU savings for using DateAdd vs. varchar conversions, while a lot relatively, isn't a lot absolutely (just fractions of a millisecond per row). However, it is still a performance difference, and it seems best to me to save every bit possible.

这篇关于SQL Query Group By Datetime 问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！