关于今天数据库学习的一些内容和总结

 

1,SQL Server COUNT() 和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

关于函数：http://www.cnblogs.com/no7dw/archive/2010/07/08/1773495.html

 函数：ABS 函数（返回数字的绝对值）

表值函数：下面是一个不带输入参数的表值函数

create function tvpoints() returns table as return ( select * from tb_users );

这个表值函数数查询所有用户表的数据

对于多语句表值函数，在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句，这些语句可生成行并将其插入将返回的表中。

以下示例创建了一个表值函数.

?

create function tvpoints() returns @points table (x float , y float ) as begin insert @points values (1,2); insert @points values (3,4); return ; end

查询表值函数跟查询普通表一样
select * from tvpoints()
返回的是一张表

带输入参数的表值函数

?

create function tvpoints2(@x AS int ,@y as int ) returns @points table (x float , y float ) as begin insert @points values (@x,@y); return ; end

事例如：

USE [SBEADMS20]
GO
/****** Object:  UserDefinedFunction [dbo].[F_CarNormalCount]    Script Date: 12/12/2012 09:57:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[F_CarNormalCount]
(
 @BeginDay INT,
 @EndDay INT
)
RETURNS @TEMP TABLE
(
 PointCode VARCHAR(32),
 PointName VARCHAR(32),
 Classify VARCHAR(32),
 CarBrand VARCHAR(64),
 Amount  INT,
 CostBalance NUMERIC(15,5)
)
BEGIN
   INSERT INTO @TEMP
 SELECT PointCode,PointName,Classify,CarBrand,Count(CarCode) AS Amount,Sum(SalePrice) AS CostBalance
 FROM
 (
  SELECT a.PointCode,
      d.PointName,
      '整车' AS Classify,
    Isnull(c.CarBrand,'') AS CarBrand,
    a.CarCode,
    b.SalePrice,
    Case b.BillType when 'IM1' then (SELECT BillDate FROM DBO.F_GetFirstBillDate(b.PointCode,b.BillType,b.CarCode))
    else b.BillDate
    end  as BillDate
   FROM T_CarInventory a
     INNER JOIN T_CarDepositing b ON a.PointCode = b.PointCode AND a.CarCode = b.Carcode  AND b.BillType LIKE 'I%1'
            AND NOT EXISTS(

                 SELECT 1 FROM T_CarDepositing T01 WHERE T01.BillType LIKE 'I%0' AND T01.PointCode = a.PointCode
                          AND T01.OriginalComplaintsCode = b.BillCode
                          AND T01.OriginalComplaintsType LIKE 'I%1'
                          AND T01.CarCode = b.CarCode
                )
     LEFT JOIN T_CarInfo c ON a.CarCode = c.CarID
     LEFT JOIN T_PointInfo d ON a.PointCode = d.PointCode  
   WHERE  a.Status in (1,2) and a.BillType <> 'IS1'
 ) AS T5
 WHERE  (DATEDIFF(DAY,ISNULL(T5.BillDate,'1980-1-1'),GETDATE()) >= @BeginDay
      AND DATEDIFF(DAY,ISNULL(T5.BillDate,'1980-1-1'),GETDATE()) <= @EndDay)
 GROUP BY PointCode,PointName,CarBrand,Classify
   RETURN
END

标量函数1：

ALTER FUNCTION [dbo].[testGetSubNodes_]
(
@nodeId int
)
RETURNS int
AS

BEGIN
    declare @nodeCount int
select @nodeCount=5 from MenuTree
    return @nodeCount
END

 

标量值函数2:

ALTER FUNCTION [dbo].[F_PointExternPropertyInfo_Get]
(
 @OperatorPointCode VARCHAR(32),
    @PointCode  VARCHAR(32),
    @PropertyName VARCHAR(128)
 ) 
RETURNS VARCHAR(128)
AS
BEGIN
 -- Declare the return variable here
 DECLARE @RetValue VARCHAR(128)
 IF @PointCode IS NULL
        SET @PointCode = @OperatorPointCode

 ;with Source as
 (
  SELECT
    T02.网点号,
    T02.属性名,
    T02.值,
    T02.属性时间
  FROM
  T_PointExternPropertyInfo T01 INNER JOIN
  (
   SELECT
    T1.PointCode   AS 网点号,
    T1.PropertyName AS 属性名,
    T1.PropertyValue AS 值,
    MAX(T1.PropertyDateTime) AS 属性时间
   FROM
    T_PointExternProperty T1
   WHERE
    T1.PointCode = @PointCode
   GROUP BY T1.PointCode,
    T1.PropertyName,
    T1.PropertyValue
  ) T02 ON T01.PropertyName = T02.属性名
 )
 
 SELECT @RetValue = Source.值 FROM (SELECT MAX(属性时间) AS 属性时间 FROM Source) T2
 INNER JOIN Source   ON Source.属性时间 = T2.属性时间
    WHERE 网点号 = @PointCode AND Source.属性名 = @PropertyName
   
    RETURN @RetValue