table1表:
流水号 专家号 A1 A2 B1 B2 C1 C2 D1 D2
09001 1001 A B C A B C A B
09001 1002 B A C A C B A B如何统计09001项目 有多少个A 多少个B 多少个C .
例如:090001项目6个A 6个B 4个C
流水号 专家号 A1 A2 B1 B2 C1 C2 D1 D2
09001 1001 A B C A B C A B
09001 1002 B A C A C B A B如何统计09001项目 有多少个A 多少个B 多少个C .
例如:090001项目6个A 6个B 4个C
from (select 流水号,A=8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'A','')),
b= 8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'B','')),
c= 8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'C',''))
from table1) a
group by 流水号
from(
select 流水号,A1 as col from table1
union all
select 流水号,A2 from table1
union all
select 流水号,B1 from table1
union all
select 流水号,B2 from table1
union all
select 流水号,C1 from table1
union all
select 流水号,C2 from table1
union all
select 流水号,D1 from table1
union all
select 流水号,D2 from table1
) t
group by 流水号,col
-- Author: flystone
-- Version:V1.001
-- Date:2010-02-23 22:18:57
-------------------------------------- Test Data: table1
If object_id('table1') is not null
Drop table table1
Go
Create table table1(流水号 nvarchar(5),专家号 int,A1 nvarchar(1),A2 nvarchar(1),B1 nvarchar(1),B2 nvarchar(1),C1 nvarchar(1),C2 nvarchar(1),D1 nvarchar(1),D2 nvarchar(1))
Go
Insert into table1
select '09001',1001,'A','B','C','A','B','C','A','B' union all
select '09001',1002,'B','A','C','A','C','B','A','B'
Go
--Start
select 流水号,sum(a)as a,sum(b)as b,sum(c)as c
from (select 流水号,A=8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'A','')) ,
b= 8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'B','')) ,
c= 8-len(replace(A1+A2+B1+B2+C1+C2+D1+D2 ,'C',''))
from table1) a
group by 流水号
--Result:
/*
流水号 a b c
----- ----------- ----------- -----------
09001 6 6 4(所影响的行数为 1 行)*/
--End
-- Author :SQL77(只为思齐老)
-- Date :2010-02-23 22:11:31
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:tb
if object_id('tb') is not null drop table tb
go
create table tb([流水号] varchar(5),[专家号] int,[A1] varchar(1),[A2] varchar(1),[B1] varchar(1),[B2] varchar(1),[C1] varchar(1),[C2] varchar(1),[D1] varchar(1),[D2] varchar(1))
insert tb
select '09001',1001,'A','B','C','A','B','C','A','B' union all
select '09001',1002,'B','A','C','A','C','B','A','B'
--------------开始查询--------------------------
create table #tb([流水号] varchar(5),a varchar(1))
declare @sql varchar(8000)
set @sql='select 流水号'
select @sql=@sql+','+name+' from tb union all select 流水号'
from syscolumns where id=object_id('tb') and name not in('流水号','专家号')
select @sql=left(@sql,len(@sql)-len('union all select 流水号'))
insert #tb exec( @sql)select 流水号,a,count(*)num from #tb group by 流水号,a
----------------结果----------------------------
/*
流水号 a num
----- ---- -----------
09001 A 6
09001 B 6
09001 C 4(所影响的行数为 3 行)
*/