求sql 语句！！
SQL: 医生 药品
1 感冒药
1 消炎药
1 消炎药
2 感冒药
2 消炎药
要求统计每个医生开了多少次药，开了多少次感冒药，感冒药占的比例有多少？ 要求在一个表中体现！求sql语句！
结果应该是这样的:
医生 开药次数 开感冒药次数 开感冒药比列
1 3 1 33%
2 2 1 50%
[最优解释]
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%
*/
[其他解释]
可以用Count（*）
[其他解释]
----------------------------
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-27 12:28:03
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
--Feb 10 2012 19:13:17
--Copyright (c) Microsoft Corporation
--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[医生] int,
[药品] varchar(6)
)
insert [test]
select 1,'感冒药' union all
select 1,'消炎药' union all
select 1,'消炎药' union all
select 2,'感冒药' union all
select 2,'消炎药'
go
select
[医生],
count(1) as 开药次数,
sum(case when [药品]='感冒药' then 1 else 0 end) as 开感冒药次数,
left(ltrim((sum(case when [药品]='感冒药' then 1 else 0 end)*100.0)/count(1)),5)+'%' as 开感冒药比列
from
test
group by
[医生]
/*
医生 开药次数 开感冒药次数 开感冒药比列
----------- ----------- ----------- --------------------------------
1 3 1 33.33%
2 2 1 50.00%
(2 行受影响)
*/
[其他解释]
with tb(医生,药品)
as(
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(药品) 开药次数,sum(case when 药品=n'感冒药' then 1 else 0 end) 开感冒药次数,
ltrim(convert(float,sum(case when 药品=n'感冒药' then 1 else 0 end)*100.0/count(药品)))+'%'
开感冒药比列 from tb group by 医生
[其他解释]
SELECT
医生
,COUNT(药品) AS 开药次数
,SUM(CASE WHEN 药品=N'感冒药' THEN 1 ELSE 0 END) AS 开感冒药次数
,LTRIM(CONVERT(NUMERIC(3,0),SUM(CASE WHEN 药品=N'感冒药' THEN 1 ELSE 0 END)*100.0/COUNT(药品)))+'%' AS 开感冒药比列
FROM t1 AS a
GROUP BY 医生
四捨五入
[其他解释]
select A.ID,A.开药次数 开药次数,B.开消炎药次数 开消炎药次数,Convert(decimal(18,2),Convert(decimal(18,2),B.开消炎药次数)/A.开药次数) 比率 from(
(select ID,count(ID) 开药次数 from tb1_A group by ID) as A left join
(select ID,count(ID) 开消炎药次数 from tb1_A where name='消炎药' group by ID) as B
on A.ID=B.ID)
[其他解释]
--创建表结构并初始化数据
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA(DID INT, MName VARCHAR(50))
INSERT INTO TA
SELECT 1,'消炎药' UNION ALL
SELECT 1,'消炎药' UNION ALL
SELECT 2,'感冒药' UNION ALL
SELECT 2,'消炎药' UNION ALL
SELECT 1,'感冒药'
--查询语句
SELECT A.DID AS '医生',A.TotalNum AS '开药次数', A.num1 AS '开感冒药次数',LTRIM(A.num1*100/A.TotalNum)+'%'
FROM (
SELECT DID,COUNT(1) TotalNum,
(SELECT COUNT(1) FROM TA AS T1 WHERE T1.DID=TA.DID AND T1.MName='感冒药') AS num1
FROM TA
GROUP BY DID)AS A
------------------查看结果------------------------
医生 开药次数 开感冒药次数
----------- ----------- ----------- -------------
1 3 1 33%
2 2 1 50%
(2 行受影响)