我有以下格式（我使用的MS SQL Server 2008）的表：
car_id | trace
1 1300275738;57.72588;11.84981;0.00026388888888888886;1300275793;57.72596;11.8529;0.001055...
跟踪值是一个CSV字符串分号作为deliminator。 在跟踪字符串的值进行分组四个四，像这样的（除换行符无）：
1300275738;57.72588;11.84981;0.00026388888888888886;
1300275793;57.72596;11.8529;0.0010555555555555555;
1300275785;57.72645;11.85242;0.007416666666666665;
1300275780;57.72645;11.85242;0.0010138888888888888;
我想要做的是创造插入时的触发器的排序基于在四组的第一个值的跟踪字符串。 所以的上述结果将成为
1300275738;57.72588;11.84981;0.00026388888888888886;1300275780;57.72645;11.85242;0.0010138888888888888;1300275785;57.72645;11.85242;0.007416666666666665;1300275793;57.72596;11.8529;0.0010555555555555555;
我试图做的是在这样一个临时表来拆分值在不同的行：
USE tempdb
GO
checkpoint
dbcc dropcleanbuffers
dbcc freeproccache
GO
--declare a variable and populate it with a comma separated string
DECLARE @SQLString VARCHAR(MAX)
SET @SQLString = (SELECT trace FROM mypev_trips.dbo.trips)
--append a comma to the string to get correct results with empty strings or strings with a single value (no commas)
SET @SQLString = @SQLString + ';';
DECLARE @X XML
SET @X = CAST('<A>' + REPLACE(@SQLString, ';', '</A><A>') + '</A>' AS XML)
SELECT t.value('.', 'nvarchar(20)')
FROM @x.nodes('/A') as x(t)
这给出了如下结果：
(No column name)
1300275738
57.72588
11.84981
0.000263888888888888
1300275780
57.72645
11.85242
0.001013888888888888
.
.
有谁知道我可以改变我的临时表返回到上排序每组四人在第一值的逗号分隔字符串？
;WITH cte(car_Id, traceXML) AS
(
SELECT car_Id, CAST('<A>' + REPLACE(trace, ';', '</A><A>') + '</A>' AS XML)
FROM dbo.cars
), cte2 AS
(
SELECT car_Id, trace, NTILE(4) OVER(ORDER BY (SELECT 1)) AS grId
FROM cte
CROSS APPLY (SELECT Tbl.Col.value('.', 'nvarchar(250)') AS trace
FROM traceXML.nodes('/A') Tbl(Col)) AS List
), cte3 AS
(
SELECT DISTINCT a.car_Id,(
SELECT ISNULL(b.trace, '') + ';'
FROM cte2 b
WHERE b.grId = a.grId
FOR XML PATH('')) AS trace
FROM cte2 a
)
SELECT DISTINCT a.car_Id,(
SELECT ISNULL(b.trace, '')
FROM cte3 b
WHERE b.car_Id = a.car_Id
FOR XML PATH('')) AS trace
FROM cte3 a
演示上SQLFiddle
最后我用两个函数和触发器。
首先函数分裂跟踪串入行
CREATE FUNCTION dbo.Split(@String varchar(MAX))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @idx2 int
declare @idx3 int
declare @idx4 int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx != 0
begin
set @idx = charindex(';',@String)
if @idx!=0
set @slice = left(@String,@idx)
else
set @slice = @String
set @String = right(@String,len(@String) - @idx)
set @idx2 = charindex(';',@String);
if @idx2!=0
set @slice = @slice + left(@String,@idx2)
set @String = right(@String,len(@String) - @idx2)
set @idx3 = charindex(';',@String);
if @idx3!=0
set @slice = @slice + left(@String,@idx3)
set @String = right(@String,len(@String) - @idx3)
set @idx4 = charindex(';',@String);
if @idx4 !=0
set @slice = @slice + left(@String,@idx4)
else
set @slice = @slice + @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx4)
if len(@String) = 0 break
if @idx4 = 0 break
end
return
end
那么功能进行排序，并从临时表合并行
CREATE FUNCTION dbo.SortCSV(@String varchar(MAX))
returns varchar(MAX)
as
begin
declare @csv varchar(MAX)
select @csv = coalesce(@csv,'') + items
from dbo.Split(@String)
order by items asc
return @csv
end
而在最后我创造一个像这样的触发
CREATE TRIGGER dbo.SortTrace
ON dbo.trips
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @trace varchar(MAX);
DECLARE @sorted_trace varchar(MAX);
SELECT @trace = trace FROM inserted;
SELECT @sorted_trace = dbo.SortCSV(@trace);
insert into dbo.trips (
car_id, start, stop, distance, created_at, updated_at, trace, energy, delta_soc, private_trip, journal_comment
) select
car_id, start, stop, distance, created_at, updated_at, @sorted_trace, energy, delta_soc, private_trip, journal_comment
from
inserted
END
GO
