请教一行含字母与数字的多列数据求和

吃荔枝的西瓜 2013-10-29 04:19:33



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、D、L、N、S、X



期望处理前样子：

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

...全文

137 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2013-10-29

打赏
举报

这个主要是通过先建个函数，然后再进行分拆，计算：


--建一个字符串拆分函数
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
*/

chen357313771 2013-10-29

打赏
举报

结果又错，稍改了下。。

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
*/

chen357313771 2013-10-29

打赏
举报

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
*/

Andy__Huang 2013-10-29

打赏
举报

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
*/