id name age sex pass type parentType
1 aa 10 m 12345 A 1
7 bb 20 f A 1
8 cc 30 m B 1
9 ee 40 f B 1
11 jack 18 f jack B 1
12 mar 35 f mar A 1
13 win 32 m B 1如何写查询语句使得结果为:
sumrate--总密码使用率 sumpass--某类型密码使用数 allpass--某类型用户的数量
sumrate parentType type sumpass allpass rate
42.86% 1 A 2 3 66.67%
42.86% 1 B 1 4 25%
insert into tb values(1 , 'aa' , 10 , 'm' , '12345', 'A' , 1 )
insert into tb values(7 , 'bb' , 20 , 'f' , '' , 'A' , 1 )
insert into tb values(8 , 'cc' , 30 , 'm' , '' , 'B' , 1 )
insert into tb values(9 , 'ee' , 40 , 'f' , '' , 'B' , 1 )
insert into tb values(11, 'jack', 18 , 'f' , 'jack' , 'B' , 1 )
insert into tb values(12, 'mar' , 35 , 'f' , 'mar' , 'A' , 1 )
insert into tb values(13, 'win' , 32 , 'm' , '' , 'B' , 1 )
goselect sumrate = cast(cast((select count(1) from tb where pass <> '')*100.0 / (select count(1) from tb ) as decimal(18,2)) as varchar) + '%' ,
parentType = max(parentType),
type ,
sumpass = (select count(1) from tb where pass <> '' and type = t.type),
allpass = (select count(1) from tb where type = t.type),
rate = cast(cast((select count(1) from tb where pass <> '' and type = t.type)*100.0 / (select count(1) from tb where type = t.type) as decimal(18,2)) as varchar) + '%'
from tb t
group by typedrop table tb/*
sumrate parentType type sumpass allpass rate
------------------------------- ----------- ---------- ----------- ----------- -------------------------------
42.86% 1 A 2 3 66.67%
42.86% 1 B 1 4 25.00%(所影响的行数为 2 行)
*/
declare @table table([id] int,[name] varchar(4),[age] int,[sex] varchar(1),[pass] varchar(5),[type] varchar(1),[parentType] int)
insert into @table
select 1,'aa',10,'m','12345','A',1 union all
select 7,'bb',20,'f','','A',1 union all
select 8,'cc',30,'m','','B',1 union all
select 9,'ee',40,'f','','B',1 union all
select 11,'jack',18,'f','jack','B',1 union all
select 12,'mar',35,'f','mar','A',1 union all
select 13,'win',32,'m','','B',1
--查询
select cast(cast(round((select count(*) from @table where pass <> '')*100.0/(select count(*) from @table),2) as dec(18,2))as varchar)+'%' as sumrate,
parentType,type,
sum(case when pass <> '' then 1 else 0 end) as sumpass,
count(1) as allpass,
cast(cast(round(sum(case when pass <> '' then 1 else 0 end)*100.0/count(1),2) as dec(18,2))as varchar)+'%' as rate
from @table
group by parentType,type
--结果
------------------------
42.86% 1 A 2 3 66.67%
42.86% 1 B 1 4 25.00%
declare @table table([id] int,[name] varchar(4),[age] int,[sex] varchar(1),[pass] varchar(5),[type] varchar(1),[parentType] int)
insert into @table
select 1,'aa',10,'m','12345','A',1 union all
select 7,'bb',20,'f','','A',1 union all
select 8,'cc',30,'m','','B',1 union all
select 9,'ee',40,'f','','B',1 union all
select 11,'jack',18,'f','jack','B',1 union all
select 12,'mar',35,'f','mar','A',1 union all
select 13,'win',32,'m','','B',1
--查询
select cast(cast(round((select count(*) from @table where pass <> '')*100.0/(select count(*) from @table),2) as dec(18,2))as varchar)+'%' as sumrate,
parentType,type,
sum(case when pass <> '' then 1 else 0 end) as sumpass,
count(1) as allpass,
cast(cast(round(sum(case when pass <> '' then 1 else 0 end)*100.0/count(1),2) as dec(18,2))as varchar)+'%' as rate
from @table
group by parentType,type
--结果
------------------------
42.86% 1 A 2 3 66.67%
42.86% 1 B 1 4 25.00%