学习啦 > 学习英语 > 专业英语 > 计算机英语 > 数据库中sum的用法

数据库中sum的用法

时间: 长思709 分享

数据库中sum的用法

  数据库中sum的用法的用法你知道吗?下面小编就跟你们详细介绍下数据库中sum的用法的用法,希望对你们有用。

  数据库中sum的用法的用法如下:

  根据BOL中解释COUNT用来返回组中的项数。

  COUNT(*)返回组中的项数。包括 NULL 值和重复项

  COUNT(ALL expression):对组中的每一行都计算 expression 并返回非空值的数量。默认值。

  COUNT(DISTINCT expression):对组中的每一行都计算 expression 并返回唯一非空值的数量。

  ID Value

  ----------- --------------------------------------------------

  1 A

  2 B

  2 B

  3 B

  4 NULL

  select COUNT(* ) from Table_1

  -----------

  5

  SELECT COUNT(Value) FROM Table_1

  -----------

  4

  SELECT COUNT(DISTINCT Value) FROM Table_1

  -----------

  2

  SUM返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。空值将被忽略

  ID Value

  ----------- -----------

  1 1

  2 2

  3 3

  4 4

  5 NULL

  6 5

  7 5

  SELECT SUM(Value) From Table_1

  -----------

  20

  SELECT SUM(DISTINCT Value) From Table_1

  -----------

  15

  以Northwind数据库中的Employees,Orders表为例,列出所有雇员的姓名以及其拥有的Order的个数。分别用Count()和SUM()实现。

  SELECT e.EmployeeID,FirstName,LastName,Count(O.EmployeeID) AS OrderCount

  FROM dbo.Employees AS E LEFT JOIN dbo.Orders AS O

  ON E.EmployeeID = O.EmployeeID

  Group By E.EmployeeID,FirstName,LastName

  SELECT e.EmployeeID,FirstName,LastName,SUM(CASE WHEN O.EmployeeID IS NULL THEN 0 ELSE 1 END) AS OrderCount

  FROM dbo.Employees AS E LEFT JOIN dbo.Orders AS O

  ON E.EmployeeID = O.EmployeeID

  Group By E.EmployeeID,FirstName,LastName

  注意的是这里一定不能用COUNT(*),因为COUNT(*)是计算行数,对于没有定单的用户也是为1。只有在JOIN的情况下,COUNT(*)才等效于Count(O.EmployeeID

  附:这两个表的定义。

  USE [Northwind]

  GO

  /****** Object: Table [dbo].[Employees] Script Date: 01/13/2012 22:57:44 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Employees](

  [EmployeeID] [int] IDENTITY(1,1) NOT NULL,

  [LastName] [nvarchar](20) NOT NULL,

  [FirstName] [nvarchar](10) NOT NULL,

  [Title] [nvarchar](30) NULL,

  [TitleOfCourtesy] [nvarchar](25) NULL,

  [BirthDate] [datetime] NULL,

  [HireDate] [datetime] NULL,

  [Address] [nvarchar](60) NULL,

  [City] [nvarchar](15) NULL,

  [Region] [nvarchar](15) NULL,

  [PostalCode] [nvarchar](10) NULL,

  [Country] [nvarchar](15) NULL,

  [HomePhone] [nvarchar](24) NULL,

  [Extension] [nvarchar](4) NULL,

  [Photo] [image] NULL,

  [Notes] [ntext] NULL,

  [ReportsTo] [int] NULL,

  [PhotoPath] [nvarchar](255) NULL,

  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

  (

  [EmployeeID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [FK_Employees_Employees] FOREIGN KEY([ReportsTo])

  REFERENCES [dbo].[Employees] ([EmployeeID])

  GO

  ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Employees]

  GO

  ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD CONSTRAINT [CK_Birthdate] CHECK (([BirthDate] < getdate()))

  GO

  ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [CK_Birthdate]

  GO

  USE [Northwind]

  GO

  /****** Object: Table [dbo].[Orders] Script Date: 01/13/2012 23:02:09 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[Orders](

  [OrderID] [int] IDENTITY(1,1) NOT NULL,

  [CustomerID] [nchar](5) NULL,

  [EmployeeID] [int] NULL,

  [OrderDate] [datetime] NULL,

  [RequiredDate] [datetime] NULL,

  [ShippedDate] [datetime] NULL,

  [ShipVia] [int] NULL,

  [Freight] [money] NULL,

  [ShipName] [nvarchar](40) NULL,

  [ShipAddress] [nvarchar](60) NULL,

  [ShipCity] [nvarchar](15) NULL,

  [ShipRegion] [nvarchar](15) NULL,

  [ShipPostalCode] [nvarchar](10) NULL,

  [ShipCountry] [nvarchar](15) NULL,

  CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED

  (

  [OrderID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])

  REFERENCES [dbo].[Customers] ([CustomerID])

  GO

  ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]

  GO

  ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeID])

  REFERENCES [dbo].[Employees] ([EmployeeID])

  GO

  ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]

  GO

  ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY([ShipVia])

  REFERENCES [dbo].[Shippers] ([ShipperID])

  GO

  ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Shippers]

  GO

  ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]

  GO

543223