首先,建立测试表,本例我们采用经典的Customer 1-->n Order结构,如下:
代码 USE test GO IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = ' Orders ' ) DROP TABLE Orders IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = ' Customers ' ) DROP TABLE Customers /* Customer 1 --> n Order */ -- Create Customer Table CREATE TABLE Customers( CustomerId UNIQUEIDENTIFIER PRIMARY KEY , Name VARCHAR ( 25 ),) GO -- Create Order Table CREATE TABLE Orders( OrderId UNIQUEIDENTIFIER PRIMARY KEY , Name VARCHAR ( 255 ), Customer_Id UNIQUEIDENTIFIER FOREIGN KEY REFERENCES Customers(CustomerId)) GO
然后,插入一些测试数据:
代码 -- insert test data for customers table INSERT INTO Customers VALUES ( ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' , ' C1 ' ) INSERT INTO Customers VALUES ( ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' , ' C2 ' ) GO -- insert test data for orders table INSERT INTO Orders VALUES ( ' 8EAA07D0-0E04-412A-8A60-3178C9C2E205 ' , ' O1 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' ) INSERT INTO Orders VALUES ( ' 78E0CDAB-A3FA-4317-9D4E-D884F91D41A8 ' , ' O2 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' ) INSERT INTO Orders VALUES ( ' D08038A9-94FB-4AFF-9E41-F8BEE0FD4384 ' , ' O3 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' ) INSERT INTO Orders VALUES ( ' 7487AE08-6313-4DB7-87D7-8F9204F07470 ' , ' O4 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' ) INSERT INTO Orders VALUES ( ' 49A76769-D074-4AF3-9707-751D34847E2A ' , ' O5 by C1 ' , ' 65DC24EF-BA13-4707-95B0-3D426C2FD0A0 ' ) INSERT INTO Orders VALUES ( ' C5C055EB-49A7-4E4C-82EA-1DB10933C78D ' , ' O6 by C2 ' , ' 32AA5A01-39B4-42D0-BEE9-9AF64171EB55 ' ) GO
建立自定义函数:
代码 IF EXISTS ( SELECT 1 FROM sysobjects WHERE name = ' fnGetCustomerOrders ' ) DROP FUNCTION fnGetCustomerOrders CREATE FUNCTION fnGetCustomerOrders( @CustomerName VARCHAR ( 25 )) RETURNS VARCHAR ( MAX ) AS BEGIN DECLARE @result VARCHAR ( MAX ) SET @result = '' SELECT @result = @result + ' , ' + o.Name from Customers c INNER JOIN Orders o ON c.CustomerId = o.Customer_Id WHERE c.Name = @CustomerName ORDER by c.Name, o.Name IF @result like ' , % ' SET @result = right ( @result , len ( @result ) - 2 ) RETURN @result END GO
测试函数:
-- test function DECLARE @customer_name VARCHAR ( 25 ) SET @customer_name = ' C1 ' SELECT ' Orders By ' + @customer_name + ' : ' + dbo.fnGetCustomerOrders( @customer_name ) GO
结果如下:
Orders By C1: O1 by C1, O3 by C1, O5 by C1