数据库中sum的用法说明

ゝ一世哀愁。 2024-04-01 17:52 80阅读 0赞

![738faa94948ff860dc8f8c698056dd70.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\]

[738faa94948ff860dc8f8c698056dd70.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4dd92ff83d8a45abaa39ac19b01e3572.jpeg

数据库中sum的用法说明

发表评论取消回复

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

相关阅读

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

相关数据库中sum与top的用法简介

相关 python中的yield用法_Python中yield用法详细说明

相关 mybatis 中的＜![CDATA[ ]]＞用法及说明

相关 PostgreSQL 中的单引号与双引号用法说明

相关 postgresql 中round函数的用法说明

相关 jQuery $.each的用法说明

相关 2>&1 的用法说明

相关 Linux shell脚本中shift的用法说明

相关队列中的循环队列用法说明