SQL组，按天计算 [英] SQL group by day, with count

问题描述

  CREATE TABLE [dbo]。[RefundProcessLog ]（
 [LogId] [bigint] IDENTITY（1,1）NOT NULL，
 [LogDate] [datetime] NOT NULL，
 [LogType] [varchar]（10）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [RefundId] [int] NULL，
 [RefundTypeId] [smallint] NULL，
 [LogMessage] [varchar]（1000）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 [LoggedBy ] [varchar]（50）COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL，
 CONSTRAINT [PK_RefundProcessLog] PRIMARY KEY CLUSTERED 
（
 [LogId] ASC 
）ON [PRIMARY] 
） ON [PRIMARY] 
 
 GO 
  

我想要的是结果列表它表示每天处理多少个不同的refundids，抛出任何NULL。

我需要编写什么SQL来产生这些结果？

$ b $我喜欢这种方法（MS SQL）：

 
  SELECT 
转换（char（8），LogDate，112），
 count（独特的RefundId）
 FROM RefundProcessing 
 GROUP BY Convert（char（8） ，LogDate，112）
  

I've got a log table in SQL Server that looks like this:

CREATE TABLE [dbo].[RefundProcessLog](
 [LogId] [bigint] IDENTITY(1,1) NOT NULL,
 [LogDate] [datetime] NOT NULL,
 [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [RefundId] [int] NULL,
 [RefundTypeId] [smallint] NULL,
 [LogMessage] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LoggedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_RefundProcessLog] PRIMARY KEY CLUSTERED 
(
 [LogId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

What I want is a list of results that represents how many different refundids were processed each day, throwing out any NULLs.

What SQL would I need to write to produce these results?

解决方案

I like this approach in (MS SQL):

SELECT 
  Convert(char(8), LogDate, 112),
  count(distinct RefundId)
FROM RefundProcessing
GROUP BY Convert(char(8), LogDate, 112)

这篇关于SQL组，按天计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！