:)
--創建測試環境
Create Table Persons
(编号 Char(3),
LastName Varchar(20),
FirstName Varchar(20),
Address Varchar(20),
City Varchar(20))
Insert Persons Select '001', 'Hansen', 'Ola', 'Timoteivn 10', 'Sandnes'
Union All Select '002', 'Svendson', 'Tove', 'Borgvn 23', 'Sandnes'
Union All Select '003', 'Pettersen', 'Kari', 'Storgt 20', 'Stavanger'
Create Table Orders
(编号 Char(3),
Company Varchar(20),
OrderNumber Int)
Insert Orders Select '001', 'Sega', 2312
Union All Select '001', 'W3School', 2312
Union All Select '001', 'Trio', 4678
Union All Select '002', 'W3School', 6798
GO
--測試
--问题一
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
union all
select * from Persons where LastName='Hansen'
GO
--問題二
--創建函數
Create Function F_Get_Orders(@编号 Char(3), @Flag Bit)
Returns Varchar(8000)
As
Begin
Declare @S Varchar(8000)
Select @S = ''
If(@Flag = 0)
Select @S = @S + ',' + Company From Orders Where 编号 = @编号 Order By OrderNumber
Else
Select @S = @S + ',' + Cast(OrderNumber As Varchar(100)) From Orders Where 编号 = @编号 Group By OrderNumber
Select @S = Stuff(@S, 1, 1, '')
Return @S
End
GO
--調用
Select 编号, dbo.F_Get_Orders(编号, 0) As Company, dbo.F_Get_Orders(编号, 1) As OrderNumber From Persons
GO
--刪除測試環境
Drop Table Persons, Orders
Drop Function F_Get_Orders
GO
--結果
/*
--问题一結果
编号LastNameFirstNameAddressCity
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes
001HansenOlaTimoteivn 10Sandnes
--问题二結果
编号CompanyOrderNumber
001Sega,W3School,Trio2312,4678
002W3School6798
003NULLNULL
*/
