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')
求高手们指导下,如何统计Day_01~Day_10列中第一个字母相同的数据的数字部分和?
其中比较特殊的是一个字段数据可以是'N4/D2/S2'
我想做个视图,对每个人做统计,统计信息的列名就为empcode、N、D、S、X、L
6 个解决方案
#1
group by left(col,1)
#2
是对一行的多列求和啊
#3
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 D L N S X
---------------------------------------------------------
EAA45463 4.00 7.50 60.50 4.00 NULL
EAA81137 NULL 16.00 29.00 NULL NULL
*/
#4
笨办法写了个
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=1
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=1
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=1
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=1
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=1
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
/*
empcode ColN ColD ColS ColX ColL
EAA45463 56.50 2.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/
#5
结果又错,稍改了下。。
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
/*
empcode ColN ColD ColS ColX ColL
EAA45463 60.50 4.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/
#6
这个主要是通过先建个函数,然后再进行分拆,可以指定按照哪个字符进行分拆,最后计算:
--建一个字符串拆分函数
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 D L N S X
EAA45463 4.0 7.5 60.5 4.0 0.0
EAA81137 0.0 16.0 29.0 0.0 0.0
*/
#1
group by left(col,1)
#2
是对一行的多列求和啊
#3
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 D L N S X
---------------------------------------------------------
EAA45463 4.00 7.50 60.50 4.00 NULL
EAA81137 NULL 16.00 29.00 NULL NULL
*/
#4
笨办法写了个
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=1
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=1
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=1
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=1
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=1
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
/*
empcode ColN ColD ColS ColX ColL
EAA45463 56.50 2.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/
#5
结果又错,稍改了下。。
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
/*
empcode ColN ColD ColS ColX ColL
EAA45463 60.50 4.00 4.00 0.00 7.50
EAA81137 29.00 0.00 0.00 0.00 16.00
*/
#6
这个主要是通过先建个函数,然后再进行分拆,可以指定按照哪个字符进行分拆,最后计算:
--建一个字符串拆分函数
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 D L N S X
EAA45463 4.0 7.5 60.5 4.0 0.0
EAA81137 0.0 16.0 29.0 0.0 0.0
*/