日期:2014-05-17 浏览次数:20533 次
USE test
go
-->生成表t1
--if object_id('t1') is not null
-- drop table t1
--Go
Create table t1([医生] smallint,[药品] nvarchar(3))
Insert into t1
Select 1,N'感冒药'
Union all Select 1,N'消炎药'
Union all Select 1,N'消炎药'
Union all Select 2,N'感冒药'
Union all Select 2,N'消炎药'
SELECT
医生
,COUNT(药品) AS 开药次数
,SUM(CASE WHEN 药品=N'感冒药' THEN 1 ELSE 0 END) AS 开感冒药次数
,LTRIM(CONVERT(TINYINT,SUM(CASE WHEN 药品=N'感冒药' THEN 1 ELSE 0 END)*100.0/COUNT(药品)))+'%' AS 开感冒药比列
FROM t1 AS a
GROUP BY 医生
/*
医生 开药次数 开感冒药次数 开感冒药比列
------ ----------- ----------- ------
1 3 1 33%
2 2 1 50%
*/