- 創(chuàng)建標量函數(shù)
USE [iROCK];
GO
/****** Object: UserDefinedFunction [dbo].[fn_selectWorkTimess] Script Date: 2021/6/9 13:51:14 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
--/*********************************************************************************************************
--建立者: HERMAN 日期﹕2021-06-09
--調(diào)用的程序﹕
--說明﹕獲取工時查秒數(shù)
--更新記錄﹕
-- 日期 更改人 更新說明
------------------ ------------- --------------------------------------------
-- 2021-06-09 HERMAN 新增
--**********************************************************************************************************/
GO
CREATE FUNCTION [dbo].[fn_selectWorkTimess]
(
@SelectTime DATETIME --查詢時間
)
RETURNS INT
AS
BEGIN
DECLARE @starttime DATETIME
DECLARE @Time DATETIME
DECLARE @Time_SS INT
SET @starttime = CONVERT(VARCHAR(10), GETDATE(), 120) + ' 08:00:00.00'; --每天八點開始
SET @Time = CAST(CAST(@SelectTime AS FLOAT)- CAST(@starttime AS FLOAT) AS DATETIME); --獲取時間差
SET @Time_SS = DATEPART(hh, @Time)*3600+DATEPART(mi, @Time)*60+DATEPART(ss, @Time); --計算時間查秒數(shù)
RETURN @Time_SS;
END;
//調(diào)用
SELECT dbo.fn_SelectWorkTimess(GETDATE())AS time
//執(zhí)行結(jié)果
time
-----------
32562
(1 行受影響)
- 創(chuàng)建表值函數(shù)
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
WHERE C.StoreID = @storeid
GROUP BY P.ProductID, P.Name
);
//調(diào)用
SELECT * FROM Sales.ufn_SalesByStore (602);
