数据库中sum的用法

Myth丶恋晨 2024-03-26 15:27 60阅读 0赞

![a1c301a8ccd7921f0bb48aad64e78f0b.jpeg][]

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

**　　数据库中sum的用法：**

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

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

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

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

ID Value

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

1 A

2 B

3 B

4 NULL

select COUNT(\* ) from Table\_1

-----------

SELECT COUNT(Value) FROM Table\_1

-----------

SELECT COUNT(DISTINCT Value) FROM Table\_1

-----------

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

ID Value

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

1 1

2 2

3 3

4 4

5 NULL

6 5

7 5

SELECT SUM(Value) From Table\_1

-----------

SELECT SUM(DISTINCT Value) From Table\_1

-----------

以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\]

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

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER ON

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\]

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

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

ALTER TABLE \[dbo\].\[Employees\] CHECK CONSTRAINT \[FK\_Employees\_Employees\]

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

ALTER TABLE \[dbo\].\[Employees\] CHECK CONSTRAINT \[CK\_Birthdate\]

USE \[Northwind\]

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

SET ANSI\_NULLS ON

SET QUOTED\_IDENTIFIER ON

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\]

ALTER TABLE \[dbo\].\[Orders\] WITH NOCHECK ADD CONSTRAINT \[FK\_Orders\_Customers\] FOREIGN KEY(\[CustomerID\])

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

ALTER TABLE \[dbo\].\[Orders\] CHECK CONSTRAINT \[FK\_Orders\_Customers\]

ALTER TABLE \[dbo\].\[Orders\] WITH NOCHECK ADD CONSTRAINT \[FK\_Orders\_Employees\] FOREIGN KEY(\[EmployeeID\])

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

ALTER TABLE \[dbo\].\[Orders\] CHECK CONSTRAINT \[FK\_Orders\_Employees\]

ALTER TABLE \[dbo\].\[Orders\] WITH NOCHECK ADD CONSTRAINT \[FK\_Orders\_Shippers\] FOREIGN KEY(\[ShipVia\])

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

ALTER TABLE \[dbo\].\[Orders\] CHECK CONSTRAINT \[FK\_Orders\_Shippers\]

ALTER TABLE \[dbo\].\[Orders\] ADD CONSTRAINT \[DF\_Orders\_Freight\] DEFAULT (0) FOR \[Freight\]

来源：[https://www.weidianyuedu.com][https_www.weidianyuedu.com]

[a1c301a8ccd7921f0bb48aad64e78f0b.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/190f787c2252464ea0d87053c73637b4.png
[https_www.weidianyuedu.com]: https://www.weidianyuedu.com

数据库中sum的用法

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL中使用SUM函数带条件的用法

相关数据库中sum的用法说明

相关数据库中sum的用法

相关数据库中cast的用法

相关 MySQL中sum()与count()的用法

相关 Oracle数据库中distinct的用法

相关 elasticsearch es sum求和统计用法

相关 Numpy之sum函数用法

相关 sum over partition by 的用法

相关 mysql数据库 count() 函数和 sum() 函数用法和区别