请教一行含字母与数字的多列数据求和
本帖最后由 u012611754 于 2013-10-29 16:20:14 编辑
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')
期望处理前样子：
empcode | Day_01 | Day_02 | Day_03 | Day_04 | Day_05 | Day_06 | Day_07 | Day_08 | Day_09 | Day_10
EAA45463 | N4/D2/S2 | N4/D2/S2 | L7.5 | N7.5 | N7.5 | N7.5 | N7.5 | N7.5 | N7.5 | N7.5
EAA81137 | X | L8 | L8 | N7.5 | N7.5 | N7.5 | X | X | X | N6.5
处理后样子：
empcode | D | L | N | S | X
EAA45463 | 4 | 7.5 | 60.5 | 4 | 0
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 cte as
(
select empcode,Day_01 as [key] from ats_test
union all select empcode,Day_02 from ats_test
union all select empcode,Day_03 from ats_test
union all select empcode,Day_04 from ats_test
union all select empcode,Day_05 from ats_test
union all select empcode,Day_06 from ats_test
union all select empcode,Day_07 from ats_test
union all select empcode,Day_08 from ats_test
union all select empcode,Day_09 from ats_test
union all select empcode,Day_10 from ats_test
),
cte2 as
(
select empcode,left([key],1) as [key],STUFF([key],1,1,'') as num
from (
SELECT empcode, SUBSTRING([key],number,CHARINDEX('/',[key]+'/',number)-number) as [key]
from cte a, master..spt_values
where [key] like '%/%' and number >=1 and type='p'
and number<len([key]) and substring('/'+[key],number,1)='/'
union all
select empcode,[key]
from cte
where [key] not like '%/%'
)t
)
select *
from
(select empcode, [key] as [key],sum(cast(num as numeric(10,2))) as num
from cte2 where ISNUMERIC(num)=1
group by empcode,[key]
) as x
pivot (sum(num)for [key] in (D,L,N,S,X)) as pvt
order by empcode
/*
empcode DLNSX
---------------------------------------------------------
EAA454634.007.5060.504.00NULL
EAA81137NULL16.0029.00NULLNULL
*/
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
*/