为文本字段实现类似"求和"的效果
在SQL Server里面,针对数字字段,我们可以用SUM函数求出多个值的总和。有的时候,我们需要为文本值实现类似的效果。例如
有一个表包含了计算机的基础信息,而另外一个表包含了IP地址信息。一个计算机可能对应了多个IP地址。如果我希望按照计算机分组,然后统计它所有的IP地址,那么该怎么办呢?
你可以通过下面的范例找到答案。我们的实现手段是通过一个函数来做。该函数接受两个参数,一个就是计算机的编号,一个则是我们希望连接字符串的分隔符。
这个函数的核心是使用了游标。 值得注意的是,下面的脚本你最好是每段注释下面的代码作为一个单独的批执行,而不要一次性全部执行。方法就是:选择每个注释下面的代码,然后按F5
--创建范例数据库
CREATE DATABASE DEMO
GO
USE DEMO
GO
--创建两个表
CREATE TABLE Computer(ID INT,ComputerName NVARCHAR(10))
GO
CREATE TABLE IPAddress(ComputerID INT,IPAddress NVARCHAR(20))
GO
--增加10笔记算机记录,每个计算机增加2个IP地址
DECLARE @I INT
SET @I=1;
WHILE(@I<10)
BEGIN
INSERT INTO Computer VALUES(@I,'Computer'+CONVERT(NVARCHAR(2),@I))
INSERT INTO IPAddress VALUES(@I,'IPAddress '+ CONVERT(NVARCHAR(2),@I))
INSERT INTO IPAddress VALUES(@I,'IPAddress '+ CONVERT(NVARCHAR(2),@I+1))
SET @I=@I+1
END
GO
--查看两个表的资料
SELECT * FROM Computer
SELECT * FROM IPAddress
--合并查看两个表单资料
SELECT C.*,I.* FROM Computer C JOIN IPAddress I ON C.ID=I.ComputerID
--创建函数来处理字符串的合并
CREATE FUNCTION SumString(@ID INT,@Dismember NVARCHAR(10))
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE IPCursor CURSOR FOR
SELECT IPAddress FROM IPAddress WHERE ComputerID=@ID
DECLARE @RESULT NVARCHAR(1000)
DECLARE @IP NVARCHAR(20)
SET @RESULT=N''
OPEN IPCursor
FETCH NEXT FROM IPCursor INTO @IP
WHILE @@FETCH_STATUS=0
BEGIN
IF(@RESULT=N'')
SET @RESULT=@IP
ELSE
SET @RESULT=@RESULT+@Dismember+@IP
FETCH NEXT FROM IPCursor INTO @IP
END
CLOSE IPCursor
DEALLOCATE IPCursor
RETURN @RESULT
END
GO
--最后做联合查询显示,其实这个时候已经用不着第二个表了
SELECT ID,ComputerName,dbo.SumString(ID,N',') AS IPAddressList FROM Computer GROUP BY ID,ComputerName
CREATE DATABASE DEMO
GO
USE DEMO
GO
--创建两个表
CREATE TABLE Computer(ID INT,ComputerName NVARCHAR(10))
GO
CREATE TABLE IPAddress(ComputerID INT,IPAddress NVARCHAR(20))
GO
--增加10笔记算机记录,每个计算机增加2个IP地址
DECLARE @I INT
SET @I=1;
WHILE(@I<10)
BEGIN
INSERT INTO Computer VALUES(@I,'Computer'+CONVERT(NVARCHAR(2),@I))
INSERT INTO IPAddress VALUES(@I,'IPAddress '+ CONVERT(NVARCHAR(2),@I))
INSERT INTO IPAddress VALUES(@I,'IPAddress '+ CONVERT(NVARCHAR(2),@I+1))
SET @I=@I+1
END
GO
--查看两个表的资料
SELECT * FROM Computer
SELECT * FROM IPAddress
--合并查看两个表单资料
SELECT C.*,I.* FROM Computer C JOIN IPAddress I ON C.ID=I.ComputerID
--创建函数来处理字符串的合并
CREATE FUNCTION SumString(@ID INT,@Dismember NVARCHAR(10))
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE IPCursor CURSOR FOR
SELECT IPAddress FROM IPAddress WHERE ComputerID=@ID
DECLARE @RESULT NVARCHAR(1000)
DECLARE @IP NVARCHAR(20)
SET @RESULT=N''
OPEN IPCursor
FETCH NEXT FROM IPCursor INTO @IP
WHILE @@FETCH_STATUS=0
BEGIN
IF(@RESULT=N'')
SET @RESULT=@IP
ELSE
SET @RESULT=@RESULT+@Dismember+@IP
FETCH NEXT FROM IPCursor INTO @IP
END
CLOSE IPCursor
DEALLOCATE IPCursor
RETURN @RESULT
END
GO
--最后做联合查询显示,其实这个时候已经用不着第二个表了
SELECT ID,ComputerName,dbo.SumString(ID,N',') AS IPAddressList FROM Computer GROUP BY ID,ComputerName
通过下面三个截图,可以很明显地看到区别