SQL查询结果分门别类

2012-08-24

SQL查询结果归类共有6个表Case表IDResultFID_SuitSuit表IDFID_ProjectProjct表IDFID_VersionVersion表IDFI

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 allselect 'Version1_Case2',50,20,'40%' union allselect 'Version1_Case3',40,20,'50%' union allselect 'Version1_Case4',40,20,'50%' union allselect 'Version1_Case5',50,20,'40%' union allselect 'Version2_Case1',50,30,'60%' union allselect 'Version2_Case2',50,20,'40%' union allselect 'Version2_Case3',50,50,'100%' union allselect 'Version2_Case4',50,20,'40%' union allselect 'Version4_Case1',50,20,'40%' union allselect 'Version4_Case2',50,20,'40%' union allselect 'Version5_Case1',50,20,'40%' union allselect 'Version5_Case2',50,20,'40%' union allselect 'Version5_Case3',50,50,'100%' union allselect 'Version3_Case1',50,20,'40%' union allselect 'Version3_Case2',50,40,'80%' union allselect '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 testgroup by LEFT([col1],8)union allselect * from test)torder by [col1]/*col1    col2    col3    col4Version1:    230    100    29%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    23%Version2_Case1    50    30    60%Version2_Case2    50    20    40%Version2_Case3    50    50    100%Version2_Case4    50    20    40%Version3:    150    90    17%Version3_Case1    50    20    40%Version3_Case2    50    40    80%Version3_Case3    50    30    60%Version4:    100    40    11%Version4_Case1    50    20    40%Version4_Case2    50    20    40%Version5:    150    90    17%Version5_Case1    50    20    40%Version5_Case2    50    20    40%Version5_Case3    50    50    100%*/我不知道你的那个百分比怎么算的。方法就这样，你可以试试改改 
 
[解决办法]
探讨

select LEFT([col1],8)+':' col1 那如果名字是无规律又怎么归类呢？
比如：
Television:    230    100    29%
Television_Case1    50    20    40%
Television_Case2    50    20    40%
Television_Case3    40    20    50%
……

[解决办法]
SQL codeselect * from(select LEFT([col1],CHARINDEX('_',col1)-1)+':' col1,sum(col2) col2,SUM(col3) col3,LTRIM((count(*)*100/(select count(1) from test)))+'%' as col4 from test group by LEFT([col1],CHARINDEX('_',col1)-1)union allselect * from test)torder by [col1]/*col1    col2    col3    col4Version1:    230    100    29%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    23%Version2_Case1    50    30    60%Version2_Case2    50    20    40%Version2_Case3    50    50    100%Version2_Case4    50    20    40%Version3:    150    90    17%Version3_Case1    50    20    40%Version3_Case2    50    40    80%Version3_Case3    50    30    60%Version4:    100    40    11%Version4_Case1    50    20    40%Version4_Case2    50    20    40%Version5:    150    90    17%Version5_Case1    50    20    40%Version5_Case2    50    20    40%Version5_Case3    50    50    100%*/
[解决办法]
SQL codeselect LEFT([col1],CHARINDEX('_',col1)-1)+':' col1,sum(col2) col2,SUM(col3) col3,LTRIM((count(*)*100/(select count(1) from test)))+'%' as col4 from test
[解决办法]
SQL codeselect col1+'  '+LTRIM(col2)+'  '+LTRIM(col3)+'  '+col4 as clofrom(select LEFT([col1],CHARINDEX('_',col1)-1)+':' col1,sum(col2) col2,SUM(col3) col3,LTRIM((count(*)*100/(select count(1) from test)))+'%' as col4 from test group by LEFT([col1],CHARINDEX('_',col1)-1)+':' )t /* cloVersion1:  230  100  29%Version2:  200  120  23%Version3:  150  90  17%Version4:  100  40  11%Version5:  150  90  17% */

热点排行

SQL Server

SQL查询结果分门别类