SQL查询结果归类
共有6个表

Case表
ID
Result
FID_Suit

Suit表
ID
FID_Project

Projct表
ID
FID_Version

Version表
ID
FID_Market

Market表
ID
FID_Product

Product表
ID
Name

查询出以Product表中字段Name =“X产品”的所有关联到Case表中的所有ID值
SQL Code：

select a1.Name ,
COUNT(*) as RUNTIME,  
sum(case when a1.Result='Passed' then 1 else 0 end) as PASSED,
str(sum(case when a1.Result='Passed' then 1 else 0 end)*100.0/COUNT(1),6,2) + '%' as PASS_RATIO  
from ARES_Case a1 inner join ARES_Suit a2
on a1.FID_Suit = a2.ID inner join ARES_Project a3
on a2.FID_Project = a3.ID inner join ARES_Version a4
on a3.FID_Version = a4.ID inner join ARES_Market a5
on a4.FID_Market = a5.ID inner join ARES_Product a6
on a6.Name = 'MachineA'
group by a1.Name order by a1.Name

得出的结果是：
Version1_Case1 50 20 40%
Version1_Case2 50 20 40%
Version1_Case3 40 20 50%
Version1_Case4 40 20 50%
Version1_Case5 50 20 40%
Version2_Case1 50 30 60%
Version2_Case2 50 20 40%
Version2_Case3 50 50 100%
Version2_Case4 50 20 40%
Version4_Case1 50 20 40%
Version4_Case2 50 20 40%
Version5_Case1 50 20 40%
Version5_Case2 50 20 40%
Version5_Case3 50 50 100%
Version3_Case1 50 20 40%
Version3_Case2 50 40 80%
Version3_Case3 50 30 60%

请问:查询是否还可以进一步归类
Version1： 230 100 43%
Version1_Case1 50 20 40%
Version1_Case2 50 20 40%
Version1_Case3 40 20 50%
Version1_Case4 40 20 50%
Version1_Case5 50 20 40%

Version2： 200 120 60%
Version2_Case1 50 30 60%
Version2_Case2 50 20 40%
Version2_Case3 50 50 100%
Version2_Case4 50 20 40%

Version3： 150 90 60%
Version3_Case1 50 20 40%
Version3_Case2 50 40 80%
Version3_Case3 50 30 60%

Version4： 100 40 40%
Version4_Case1 50 20 40%
Version4_Case2 50 20 40%

Version5： 150 90 60%
Version5_Case1 50 20 40%
Version5_Case2 50 20 40%
Version5_Case3 50 50 100%




------解决方案--------------------

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test]([col1] varchar(14),[col2] int,[col3] int,[col4] varchar(4))
insert [test]
select 'Version1_Case1',50,20,'40%' union all
select 'Version1_Case2',50,20,'40%' union all
select 'Version1_Case3',40,20,'50%' union all
select 'Version1_Case4',40,20,'50%' union all
select 'Version1_Case5',50,20,'40%' union all
select 'Version2_Case1',50,30,'60%' union all
select 'Version2_Case2',50,20,'40%' union all
select 'Version2_Case3',50,50,'100%' union all
select 'Version2_Case4',50,20,'40%' union all
select 'Version4_Case1',50,20,'40%' union all
select 'Version4_Case2',50,20,'40%' union all
select 'Version5_Case1',50,20,'40%' union all
select 'Version5_Case2',50,20,'40%' union all
select 'Version5_Case3',50,50,'100%' union all
select 'Version3_Case1',50,20,'40%' union all
select 'Version3_Case2',50,40,'80%' union all
select 'Version3_Case3',50,30,'60%'



select * from(
select LEFT([col1],8)+':' col1,sum(col2) col2,SUM(col3) col3,
LTRIM((count(*)*100/(select count(1) from test)))+'%' as col4
 from test
group by LEFT([col1],8)
union all
select * from test
)t
order by [col1]

/*
col1    col2    col3    col4
Version1:    230    100    29%
Version1_Case1    50    20    40%
Version1_Case2    50    20    40%
Version1_Case3    40    20    50%
Version1_Case4    40