本文介绍了SQL中的String.Join的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想从名为 RMA(简化)的表中进行选择:
i want to select from a Table called RMA(simplified):
idRMA| RMA_Number ----------------------- 1 RMA0006701 2 RMA0006730 3 RMA0006736 4 RMA0006739 5 RMA0006742RMA 和 tdefSymptomCode 之间有一个名为 trelRMA_SymptomCode 的联结表:
There is a junction table between RMA and tdefSymptomCode called trelRMA_SymptomCode:
fiSymptomCode| fiRMA ----------------------- 1 1 1 2 2 2 5 3 7 3 8 3 2 5 3 5 4 5 5 5为了完整起见,这是tdefSymptomCode:
idSymptomCode SymptomCodeNumber SymptomCodeName 1 0000 Audio problem 2 0100 SIM problem 3 0200 Appearance problem 4 0300 Network problem 5 0500 On/Off problem问:
每个 RMA 可以有 0-5 个症状代码.如何在标量值函数中将 SymptomCodeNumber 与分隔符(如:")连接在一起,以便我只得到一个 varchar 值作为结果.
这样的东西(其中 getRmaSymptomCodes 是一个 SVF):
Something like this(where getRmaSymptomCodes is a SVF):
SELECT idRMA, RMA_Number, dbo.getRmaSymptomCodes(idRMA,':') AS Symptoms FROM RMA这可能是 3 种不同 RMA 的症状(都只有一种症状):
This could be the symptoms of 3 different RMA's (all have exactly one symptom):
RMA_Number SymptomCodeNumber RMA0004823 0100 RMA0004823 0200 RMA0000083 0300 RMA0000084 0300 RMA0000084 0400这应该连接为:
RMA0004823 0100:0200 RMA0000083 0300 RMA0000084 0300:0400提前致谢
更新:感谢我创建了这个工作函数
Update: Thanks to all i have created this working function
CREATE FUNCTION [dbo].[getRmaSymptomCodes] ( @idRMA int, @delimiter varchar(5) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Codes VARCHAR(8000) SELECT @Codes = COALESCE(@Codes + @delimiter, '') + tdefSymptomCode.SymptomCodeNumber FROM RMA INNER JOIN trelRMA_SymptomCode ON RMA.IdRMA = trelRMA_SymptomCode.fiRMA INNER JOIN tdefSymptomCode ON trelRMA_SymptomCode.fiSymptomCode = tdefSymptomCode.idSymptomCode where idRMA=@idRMA order by SymptomCodeNumber return @Codes END 推荐答案这样做可以吗?
DECLARE @Codes VARCHAR(8000) SELECT @Codes = COALESCE(@Codes + ', ': '') + tdefSymptomCode.SymptomCodeNumber FROM RMA INNER JOIN trelRMA_SymptomCode ON RMA.IdRMA = trelRMA_SymptomCode.fiRMA INNER JOIN tdefSymptomCode ON trelRMA_SymptomCode.fiSymptomCode = tdefSymptomCode.idSymptomCode where idRMA=2 order by SymptomCodeNumber return @Codes更多推荐
SQL中的String.Join
发布评论