我需要获取每个行项目的查询结果,并构建一个以分号分隔的组成该项目的材料列表。
架构构成:
表格:
LineItems(唯一项目列表)
LineItems_Materials(多对多)
材料(独特材料清单)
行项目:
ID|行项目
1|'1A.1'
LineItems_Materials:
ID|行项目 ID|材质 ID
1|1|1
2|1|2
3|1|3
材料:
编号|材料
1|混凝土
2|钢
3|污垢
因此,对于行项目 1 (1A.1),我希望它显示混凝土;钢;泥土
我知道我可以编写一个函数来做到这一点。我在函数中使用了 CTE...我也可以使用 while 循环。还有其他方法会更好吗?
这是我所拥有的(脚本将构建对象、加载数据和创建函数):
SCRIPT:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[UFN_LineItem_Materials]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [test].[UFN_LineItem_Materials]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems]') AND type in (N'U'))
DROP TABLE [test].[LineItems]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Materials]') AND type in (N'U'))
DROP TABLE [test].[Materials]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems_Materials]') AND type in (N'U'))
DROP TABLE [test].[LineItems_Materials]
GO
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'test')
DROP SCHEMA [test]
GO
CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO
Create Table test.Materials(
MaterialID INT IDENTITY(1,1),
Material varchar(100));
Insert Into test.Materials
Values('Concrete');
Insert Into test.Materials
Values('Steel');
Insert Into test.Materials
Values('Dirt');
GO
Create Table test.LineItems_Materials(
LineItemMaterialID INT IDENTITY(1,1),
LineItemID INT,
MaterialID INT)
GO
Insert Into test.LineItems_Materials
Select 1,1
UNION
Select 1,2
UNION
Select 1,3
GO
CREATE TABLE [test].[LineItems](
[LineItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemNumber] [varchar](25) NULL
) ON [PRIMARY]
GO
Insert Into [test].[LineItems]
Select '1A.1'
GO
-------------------------------------------------------------
--Build Material Strings (;) example: List of Materials
------------------------------------------------------------
CREATE FUNCTION test.UFN_LineItem_Materials(@LineItemID INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @Materials Varchar(100) = ''
;with CTE
AS(
Select lm.LineItemID,m.MaterialID,m.Material
from test.LineItems_Materials lm
inner join test.Materials m on lm.MaterialID = m.MaterialID
Where lm.LineItemID = @LineItemID
)
Select @Materials += ';' + c.Material
from CTE c;
SET @Materials = substring(@Materials,2,LEN(@Materials)-1);
RETURN @Materials;
END
GO
Select lm.LineItemID,test.UFN_LineItem_Materials(lm.LineItemID) Materials
From test.Materials m
inner join test.LineItems_Materials lm on m.MaterialID = lm.MaterialID
Where m.Material = 'Concrete'
还有其他想法吗?
始终感谢反馈
--S