select
(select count(*) from TBData where Field1 = 'AAA' and 类型=a.类型) as AAA数,
(select count(*) from TBData where Field1 = 'BBB' and 类型=a.类型) as BBB数,
count(*) as Field3_Sum,
(select sum(Field3) from TBData where 类型=a.类型) as Field3_Sum,
(select count(*) from TBData where Field4= 'CCCC' and 类型=a.类型) as Field3_DCount,
(select count(*) from TBData where Field4= 'DDDD' and 类型=a.类型) as Field4_DCount
from TBData a
group by 类型
(select count(*) from TBData where Field1 = 'AAA' and 类型=a.类型) as AAA数,
(select count(*) from TBData where Field1 = 'BBB' and 类型=a.类型) as BBB数,
count(*) as Field3_Sum,
(select sum(Field3) from TBData where 类型=a.类型) as Field3_Sum,
(select count(*) from TBData where Field4= 'CCCC' and 类型=a.类型) as Field3_DCount,
(select count(*) from TBData where Field4= 'DDDD' and 类型=a.类型) as Field4_DCount
from TBData a
group by 类型
AAA数=sum(case Field1 when 'AAA' then 1 else 0 end),
BBB数=sum(case Field1 when 'BBB' then 1 else 0 end),
count(*) as Fd2_数,
sum(Field3) as Field3_Sum
Field4_Ccount=sum(case Field4 when 'CCC' then 1 else 0 end),
Field4_DCount=sum(case Field4 when 'DDD' then 1 else 0 end)
from TBData
group by 类型
Create Table TBData
(ID Int Identity(1,1),
类型 Nvarchar(10),
Field1 Varchar(10),
Field2 Nvarchar(10),
Field3 Int,
Field4 Varchar(10))
--插入数据
Insert TBData Values(N'A类','AAA',N'数据1',10,'CCCC')
Insert TBData Values(N'B类','BBB',N'数据2',12,'DDDD')
Insert TBData Values(N'B类','AAA',N'数据3',45,'DDDD')
Insert TBData Values(N'A类','AAA',N'数据4',34,'CCCC')
--测试
Select
类型,
SUM(Case Field1 When 'AAA' Then 1 Else 0 End) As AAA数,
SUM(Case Field1 When 'BBB' Then 1 Else 0 End) As BBB数,
Count(Field2) As Fd2_数,
SUM(Field3) As Field3_Sum,
SUM(Case Field4 When 'CCCC' Then 1 Else 0 End) As Field4_Ccount,
SUM(Case Field4 When 'DDDD' Then 1 Else 0 End) As Field4_DCount
from TBData
Group By 类型
Order By 类型
--删除测试环境
Drop Table TBData
--结果
/*
类型 AAA数 BBB数 Fd2_数 Field3_Sum Field4_Ccount Field4_DCount
A类 2 0 2 44 2 0
B类 1 1 2 57 0 2
*/