请教含字母与数字的多列数据求和
IF OBJECT_ID('tempdb..#ats_test','U') IS NOT NULL DROP TABLE #ats_test
CREATE TABLE #ats_test
(
empcode NVARCHAR(40),
Day_01 NVARCHAR(10),
Day_02 NVARCHAR(10),
Day_03 NVARCHAR(10),
Day_04 NVARCHAR(10),
Day_05 NVARCHAR(10),
Day_06 NVARCHAR(10),
Day_07 NVARCHAR(10),
Day_08 NVARCHAR(10),
Day_09 NVARCHAR(10),
Day_10 NVARCHAR(10)
)
insert into #ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into #ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')
IF OBJECT_ID('tempdb..#TempA','U') IS NOT NULL DROP TABLE #TempA
CREATE TABLE #TempA(
Id INT IDENTITY(1,1)
,empcode NVARCHAR(40)
,Days NVARCHAR(6)
,Num NVARCHAR(10)
,ColN NVARCHAR(10)
,ColD NVARCHAR(10)
,ColS NVARCHAR(10)
,ColX NVARCHAR(10)
,ColL NVARCHAR(10)
)
INSERT INTO #TempA
SELECT empcode,DS AS Days,Num,0,0,0,0,0
FROM #ats_test P
UNPIVOT
(
Num FOR DS IN (Day_01,Day_02,Day_03,Day_04,Day_05,Day_06,Day_07,Day_08,Day_09,Day_10)
)AS uppvt
DECLARE @Cnt INT=(SELECT Max(Id) FROM #TempA)
DECLARE @Txt NVARCHAR(10)='',@Num NVARCHAR(10)=''
DECLARE @Index INT
WHILE @Cnt>0
BEGIN
SELECT @Txt=Num FROM #TempA WHERE Id=@Cnt
WHILE LEN(@Txt)>0
BEGIN
IF CHARINDEX('/',@Txt)>0
BEGIN
SET @Index=CHARINDEX('/',@Txt)
SET @Num=LEFT(@Txt,@Index-1)
IF CHARINDEX('N',@Num)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('D',@Num)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('S',@Num)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('X',@Num)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('L',@Num)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Num,1,1,'')='' THEN '0' ELSE STUFF(@Num,1,1,'') END WHERE Id=@Cnt
SET @Txt=STUFF(@Txt,1,CHARINDEX('/',@Txt),'')
END ELSE
BEGIN
IF CHARINDEX('N',@Txt)>0
UPDATE #TempA SET ColN=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('D',@Txt)>0
UPDATE #TempA SET ColD=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('S',@Txt)>0
UPDATE #TempA SET ColS=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('X',@Txt)>0
UPDATE #TempA SET ColX=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
IF CHARINDEX('L',@Txt)>0
UPDATE #TempA SET ColL=CASE WHEN STUFF(@Txt,1,1,'')='' THEN '0' ELSE STUFF(@Txt,1,1,'') END WHERE Id=@Cnt
SET @Txt=STUFF(@Txt,1,LEN(@Txt),'')
END
END
SET @Cnt=@Cnt-1
END
SElECT empcode
,SUM(CAST(ColN AS DECIMAL(9,2))) AS ColN
,SUM(CAST(ColD AS DECIMAL(9,2))) AS ColD
,SUM(CAST(ColS AS DECIMAL(9,2))) AS ColS
,SUM(CAST(ColX AS DECIMAL(9,2))) AS ColX
,SUM(CAST(ColL AS DECIMAL(9,2))) AS ColL
FROM #TempA
GROUP BY empcode
/*
empcodeColNColDColSColXColL
EAA4546360.504.004.000.007.50
EAA8113729.000.000.000.0016.00
*/
[解决办法]
这个主要是通过先建个函数，然后再进行分拆，可以指定按照哪个字符进行分拆，最后计算：
--建一个字符串拆分函数
if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
drop function dbo.f_splitSTR
go
create function dbo.f_splitSTR
(
@s varchar(8000), --要分拆的字符串
@split varchar(10) --分隔字符
)
returns @re table( --要返回的临时表
col varchar(1000) --临时表中的列
)
as
begin
declare @len int
set @len = LEN(@split) --分隔符不一定就是一个字符，可能是2个字符
while CHARINDEX(@split,@s) >0
begin
insert into @re
values(left(@s,charindex(@split,@s) - 1))
set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖：字符串以及分隔符
end
insert into @re values(@s)
return --返回临时表
end
go
If object_id('ats_test') is not null
drop table ats_test
go
--建表
CREATE TABLE ats_test
(empcode NVARCHAR(40),
Day_01 NVARCHAR(10),
Day_02 NVARCHAR(10),
Day_03 NVARCHAR(10),
Day_04 NVARCHAR(10),
Day_05 NVARCHAR(10),
Day_06 NVARCHAR(10),
Day_07 NVARCHAR(10),
Day_08 NVARCHAR(10),
Day_09 NVARCHAR(10),
Day_10 NVARCHAR(10))
insert into ats_test values ('EAA45463','N4/D2/S2','N4/D2/S2','L7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5','N7.5')
insert into ats_test values ('EAA81137','X','L8','L8','N7.5','N7.5','N7.5','X','X','X','N6.5')
go
;with t
as
(
select empcode,day_01,col from ats_test a cross apply dbo.f_splitSTR(a.day_01,'/') union all
select empcode,day_02,col from ats_test a cross apply dbo.f_splitSTR(a.day_02,'/') union all
select empcode,day_03,col from ats_test a cross apply dbo.f_splitSTR(a.day_03,'/') union all
select empcode,day_04,col from ats_test a cross apply dbo.f_splitSTR(a.day_04,'/') union all
select empcode,day_05,col from ats_test a cross apply dbo.f_splitSTR(a.day_05,'/') union all
select empcode,day_06,col from ats_test a cross apply dbo.f_splitSTR(a.day_06,'/') union all
select empcode,day_07,col from ats_test a cross apply dbo.f_splitSTR(a.day_07,'/') union all
select empcode,day_08,col from ats_test a cross apply dbo.f_splitSTR(a.day_08,'/') union all
select empcode,day_09,col from ats_test a cross apply dbo.f_splitSTR(a.day_09,'/') union all
select empcode,day_10,col from ats_test a cross apply dbo.f_splitSTR(a.day_10,'/')
)
select empcode,
sum(case when left(col,1) = 'D' then cal else 0.0 end) as D,
sum(case when left(col,1) = 'L' then cal else 0.0 end) as L,
sum(case when left(col,1) = 'N' then cal else 0.0 end) as N,
sum(case when left(col,1) = 'S' then cal else 0.0 end) as S,
sum(case when left(col,1) = 'X' then cal else 0.0 end) as X
from
(
select *,case when len(col) > 1
then cast(substring(col,2,len(col)-1) as numeric(20,1))
else 0
end as cal
from t
)t
group by empcode
/*
empcode DL N S X
EAA454634.07.5 60.5 4.00.0
EAA811370.016.0 29.00.00.0
*/