如何在特定的sqlserver 2005查询中使用Sql函数？ [英] How can I Use Sql Function in a perticular sqlserver 2005 query?

问题描述

我有一张这样的桌子

I have a table like this

Create table Employee
(
EmpID	int,
FirstName	varchar(50),
LastName	varchar(50),
Salary	int,
Address	varchar(100)
)

- 插入记录： -

--Insert record:-

Insert into Employee values(1,'Hem Raj','Thakur',12000.00,'Mandi H.P.')
Insert into Employee values(2,'Khem Raj','Thakur',12500.00,'Bilas Pur H.P.')
Insert into Employee values(3,'Karan Kumar','Thakur',15600.00,'Hamir Pur H.P.')

select * from Employee

- 创建函数

--create function

Alter function GetSumSalary
(@Salary int
)
returns varchar(101)
as begin return(SELECT SUM(Salary) AS Salary
FROM Employee GROUP BY EmpID WITH ROLLUP)
end

我想要Resu就像这样： -

I Wanna Result like this:-

EmpID FirstName LastName Salary Address
1	Hem Raj	     Thakur  12000	Mandi H.P.
2	Khem Raj	 Thakur	 12500	Bilas Pur H.P.
3	Karan Kumar	 Thakur	 15600	Hamir Pur H.P.
		     TotalSalary 40100	

- 如何调用此函数作为一个特定的sql server查询。



我的查询是这样的

--How can I call This function ia a perticular sql server query.

my query is like this

select EmpID,FirstName,LastName,Address,dbo.GetSumSalary(Salary)as Salary from Employee

请帮助我



提前致谢

Er.Hem Raj Thakur

Please Help Me

Thanks in advance
Er.Hem Raj Thakur

推荐答案

试试这个：

Try this:

SELECT EmpID, FirstName, LastName, Address, Salary
FROM (
    SELECT EmpID, FirstName, LastName, Address, SUM(Salary)as Salary
    FROM Employee
    GROUP BY EmpID, FirstName, LastName, Address
    UNION ALL
    SELECT NULL AS EmpID, NULL AS FirstName, NULL AS LastName, 'Total Salary:' AS Address, SUM(Salary)as Salary
    FROM Employee
    GROUP BY EmpID, FirstName, LastName, Address
) AS T

ALTER function GetSumSalary1
()
returns varchar(101)
as begin 
declare @Return varchar(101)
    select @Return = (SELECT SUM(Salary) AS Salary FROM Employee) 
return @Return
END

select EmpID,FirstName,LastName,Address,Salary from Employee
 UNION 
Select null as EmpID,null as FirstName,null as LastName,'Total Salary' as Address, dbo.GetSumSalary1() from Employee 

这篇关于如何在特定的sqlserver 2005查询中使用Sql函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！