特黄特色三级在线观看免费,看黄色片子免费,色综合久,欧美在线视频看看,高潮胡言乱语对白刺激国产,伊人网成人,中文字幕亚洲一碰就硬老熟妇

學(xué)習(xí)啦 > 學(xué)習(xí)英語(yǔ) > 專(zhuān)業(yè)英語(yǔ) > 計(jì)算機(jī)英語(yǔ) > 數(shù)據(jù)庫(kù)中sum的用法

數(shù)據(jù)庫(kù)中sum的用法

時(shí)間: 長(zhǎng)思709 分享

數(shù)據(jù)庫(kù)中sum的用法

  數(shù)據(jù)庫(kù)中sum的用法的用法你知道嗎?下面小編就跟你們?cè)敿?xì)介紹下數(shù)據(jù)庫(kù)中sum的用法的用法,希望對(duì)你們有用。

  數(shù)據(jù)庫(kù)中sum的用法的用法如下:

  根據(jù)BOL中解釋COUNT用來(lái)返回組中的項(xiàng)數(shù)。

  COUNT(*)返回組中的項(xiàng)數(shù)。包括 NULL 值和重復(fù)項(xiàng)

  COUNT(ALL expression):對(duì)組中的每一行都計(jì)算 expression 并返回非空值的數(shù)量。默認(rèn)值。

  COUNT(DISTINCT expression):對(duì)組中的每一行都計(jì)算 expression 并返回唯一非空值的數(shù)量。

  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返回表達(dá)式中所有值的和或僅非重復(fù)值的和。SUM 只能用于數(shù)字列??罩祵⒈缓雎?/p>

  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數(shù)據(jù)庫(kù)中的Employees,Orders表為例,列出所有雇員的姓名以及其擁有的Order的個(gè)數(shù)。分別用Count()和SUM()實(shí)現(xiàn)。

  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(*),因?yàn)镃OUNT(*)是計(jì)算行數(shù),對(duì)于沒(méi)有定單的用戶(hù)也是為1。只有在JOIN的情況下,COUNT(*)才等效于Count(O.EmployeeID

  附:這兩個(gè)表的定義。

  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