求高手帮忙写一条ＳＱＬ语句

[fengcb]

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语句搞定，请高手帮帮忙

[fcuandy]

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

动态行转列略.

[-晴天]

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

[twgxzjk]

同意楼上

[wzy_love_sly]

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

name 产品推广数 业务联络数 产品销售数
张三 0 0 0
李四 2 0 0
王五 1 1 0
赵六 0 0 0

[internetroot]

--仅供参考，随手敲的

select 姓名=s.[name],
       产品推广数=(select count(staffname) from work where type1 ='产品推广' and staffname=s.[name]),
       业务联络数=(select count(staffname) from work where type1 ='业务联络' and staffname=s.[name]),
       产品销售数=(select count(staffname) from work where type1 ='销售产品' and staffname=s.[name]) 
from staff s

[-晴天]

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

少写了括号.

[hyqwan11112]

--左连接加一个行列转换
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),
业务联络数=sum(case when type='业务联络' then 1 else 0 end),
产品销售数=sum(case when type='产品销售' then 1 else 0 end)
from @staff a left join @work b on a.name=b.staffname
group by a.id,a.[name]

[tushadongjing]

create table #staff(id int,[Name] sysname)
create table #work(id int,staffname sysname,type1 sysname)

insert into #staff
select 1, N'张三' union
select 2, N'李四' union
select 3, N'王五' union
select 4, N'赵六'

insert into #work
select 1 , N'李四' , N'产品推广' union
select 2 , N'李四' , N'产品推广' union
select 3 , N'王五' , N'业务联络' union
select 4 , N'王五' , N'产品推广' union
select 5 , N'张三' , N'销售产品'


select
staff staffname
,sum(a) 产品推广数
,sum(b) 业务联络数
,sum(c) 产品销售数
from
(
select tempstaffname as staff
,(case when temptype1 = N'产品推广' then type1Count else 0 end) as a
,(case when temptype1 = N'业务联络' then type1Count else 0 end) as b
,(case when temptype1 = N'销售产品' then type1Count else 0 end) as c
from(select [name] as tempstaffname,type1 temptype1 ,count(type1) as type1Count
from #staff left join #work on [Name] = staffname
group by [name],type1) as temp1
) as temp2
group by staff

drop table #staff
drop table #work

[tushadongjing]

暈

[ilovewalk]

怎么一天到晚都是pivot.

[113244]

mark...

[nalnait]

pivot

[utpcb]

晕慢了

[zldong]

同意二楼的观点。

[zlp321002]

DECLARE @STAFF TABLE(ID INT, NAME VARCHAR(10))
INSERT INTO @STAFF(ID,NAME)
SELECT 1,'张三'
UNION ALL
SELECT 2,'李四'
UNION ALL
SELECT 3,'王五'
UNION ALL
SELECT 4,'赵六'

DECLARE @WORK TABLE(ID INT, STAFFNAME VARCHAR(10),TYPE1 VARCHAR(10))
INSERT INTO @WORK (ID,STAFFNAME,TYPE1)
SELECT 1,'李四','产品推广'
UNION ALL
SELECT 2,'李四','产品推广'
UNION ALL
SELECT 3,'王五','业务联络'
UNION ALL
SELECT 4,'王五','产品推广'
UNION ALL
SELECT 5,'张三','销售产品'

SELECT * FROM 
(
SELECT TB.ID,TB.NAME,TYPE1 FROM @WORK TA
	RIGHT JOIN @STAFF TB
ON TA.STAFFNAME=TB.NAME
) TA 
PIVOT
	(COUNT(TYPE1)
	 FOR TYPE1 IN ([产品推广],[业务联络],[销售产品])
	) AS PT 
/*
(4 行受影响)

(5 行受影响)
ID          NAME       产品推广        业务联络        销售产品
----------- ---------- ----------- ----------- -----------
2           李四         2           0           0
3           王五         1           1           0
1           张三         0           0           1
4           赵六         0           0           0

(4 行受影响)

*/

[zlp321002]

SELECT * FROM 
(
SELECT TB.ID,TB.NAME,TYPE1 FROM @WORK TA
	RIGHT JOIN @STAFF TB
ON TA.STAFFNAME=TB.NAME
) TA 
PIVOT
	(COUNT(TYPE1)
	 FOR TYPE1 IN ([产品推广],[业务联络],[销售产品])
	) AS PT 
ORDER BY ID
/*
ID          NAME       产品推广        业务联络        销售产品
----------- ---------- ----------- ----------- -----------
1           张三         0           0           1
2           李四         2           0           0
3           王五         1           1           0
4           赵六         0           0           0

(4 行受影响)
*/

[seairhh]

几位红星闪闪的大大们已经有好的答案鸟。

楼主结贴了吧。