日期:2014-05-18  浏览次数:20611 次

求高手帮忙写一条SQL语句
staff表结枸
id name
1 张三
2 李四
3 王五
4 赵六
work
id staffname type1
1 李四 产品推广
2 李四 产品推广
3 王五 业务联络
4 王五 产品推广
5 张三 销售产品 

要求得到如下表
姓名 产品推广数 业务联络数 产品销售数 
张三 0 0 1
李四 2 0 0
王五 0 1 0
赵六 0 0 0
能否用一条sql语句搞定,请高手帮帮忙

------解决方案--------------------
SQL code
select a.name,产品数=SUM(CASEWHEN type1='产品推广' THEN 1 ELSE 0 END),
              业务数=SUM(........='业务联络' THEN 1 ELSE 0 END),
              ...略
FROM staff a
LEFT JOIN work b
     ON a.name=b.staffname
GROUP BY a.name

------解决方案--------------------
SQL code

select a.name,count(isnull(select 1 from work where staffname=a.name and type1='产品推广'),0)) as 产品推广数,
  count(isnull(select 1 from work where staffname=a.name and type1='业务联络'),0)) as 业务联络数,
  count(isnull(select 1 from work where staffname=a.name and type1='产品销售'),0)) as 产品销售数
from staff a group by a.name

------解决方案--------------------
同意楼上

------解决方案--------------------
SQL code
declare @staff table(id int,name varchar(50))
insert into @staff select 1,'张三'
insert into @staff select 2,'李四'
insert into @staff select 3,'王五'
insert into @staff select 4,'赵六'
declare @work table(id int,staffname varchar(50),type varchar(50))
insert into @work select 1,'李四','产品推广'
insert into @work select 2,'李四','产品推广'
insert into @work select 3,'王五','业务联络'
insert into @work select 4,'王五','产品推广'
insert into @work select 5,'张三','销售产品'

select a.name,
sum(case when type='产品推广' then 1 else 0 end) as '产品推广数',
sum(case when type='业务联络' then 1 else 0 end) as '业务联络数',
sum(case when type='产品销售' then 1 else 0 end) as '产品销售数'
from @staff a left join @work b on a.name=b.staffname
group by a.id,a.name order by a.id