求一个SQL语句~ 谢谢

[mjfei]

create table b_user(u_id nvarchar(50),u_name nvarchar(50), u_dept nvarchar(50))
insert into b_user values('1','张三','1')
insert into b_user values('2','李四','1')
insert into b_user values('3','王二','1')
insert into b_user values('4','麻子','1')
insert into b_user values('5','小明','1')
create table b_kpi(id nvarchar(50),u_id nvarchar(50), kpi1 nvarchar(50),kpi2 nvarchar(50),t_id nvarchar(50))
insert into b_kpi values('1','4','48','50','1')
insert into b_kpi values('2','4','44','49','1')
insert into b_kpi values('3','4','45','42','1')
insert into b_kpi values('4','1','38','40','1')
insert into b_kpi values('5','2','39','39','1')
insert into b_kpi values('6','4','40','38','2')
create table b_file(t_id nvarchar(50),t_name nvarchar(50))
insert into b_file values('1','一季度绩效考核')
insert into b_file values('2','半年绩效考核')

主要是有多个人为同一个人打分（单独生成一条记录），最后生成报表的时候要对某个考核项目（比如：一季度绩效考核）对有多个记录的求平均值。

[再来壹串]

;WITH cte AS (
	SELECT u_id,t_id,SUM(CONVERT(DECIMAL(18,2),kpi1)) AS kpi1,SUM(CONVERT(DECIMAL(18,2),kpi2)) AS kpi2 
	FROM b_kpi
	GROUP BY u_id,t_id
)
SELECT a.u_name,ISNULL(b.kpi1,0) AS kpi1,ISNULL(b.kpi2,0) AS kpi2,c.t_name 
FROM b_user a LEFT JOIN cte b ON a.u_id = b.u_id
LEFT JOIN b_file c ON a.u_dept=c.t_id

这样？

[棉花棒棒糖]

select u_id,u_name,kpi1,kpi2,case t_id when '1' then '一季度绩效考核' else '' end 绩效考核项目
 from
(
select   a.u_id, a.u_name, avg (cast(isnull (b.kpi1,0)as real)) kpi1,avg(cast (isnull (b.kpi2,0)as real))kpi2,isnull (b.t_id,'')t_id from b_user  a
left join b_kpi b
on a.u_id=b.u_id 
group by a.u_id, a.u_name,b.t_id)b

[棉花棒棒糖]

select u_id,u_name,isnull (kpi1,0)kpi1,isnull (kpi2,0)kpi2,case t_id when '1' then '一季度绩效考核' when '2' then '二季度绩效考核' else '' end 绩效考核项目  from( select   a.u_id, a.u_name, avg (cast(isnull (b.kpi1,0)as real)) kpi1,avg(cast (isnull (b.kpi2,0)as real))kpi2,isnull (b.t_id,'')t_id from b_user  a left join b_kpi b on a.u_id=b.u_id  group by a.u_id, a.u_name,b.t_id)b

[棉花棒棒糖]

select u_id,u_name,isnull (kpi1,0)kpi1,isnull (kpi2,0)kpi2,'一季度绩效考核' t_id from( select a.u_id, a.u_name, avg (cast(isnull (b.kpi1,0)as real)) kpi1,avg(cast (isnull (b.kpi2,0)as real))kpi2,isnull (b.t_id,'')t_id from b_user a left join b_kpi b on a.u_id=b.u_id and b.t_id ='1' group by a.u_id, a.u_name,b.t_id)b ================= 1 张三 38 40 一季度绩效考核 2 李四 39 39 一季度绩效考核 3 王二 0 0 一季度绩效考核 4 麻子 45.6666666666667 47 一季度绩效考核 5 小明 0 0 一季度绩效考核

[Landa_Jimmy]

create table b_user(u_id nvarchar(50),u_name nvarchar(50), u_dept nvarchar(50))
insert into b_user values('1','张三','1')
insert into b_user values('2','李四','1')
insert into b_user values('3','王二','1')
insert into b_user values('4','麻子','1')
insert into b_user values('5','小明','1')
create table b_kpi(id nvarchar(50),u_id nvarchar(50), kpi1 nvarchar(50),kpi2 nvarchar(50),t_id nvarchar(50))
insert into b_kpi values('1','4','48','50','1')
insert into b_kpi values('2','4','44','49','1')
insert into b_kpi values('3','4','45','42','1')
insert into b_kpi values('4','1','38','40','1')
insert into b_kpi values('5','2','39','39','1')
insert into b_kpi values('6','4','40','38','2')
create table b_file(t_id nvarchar(50),t_name nvarchar(50))
insert into b_file values('1','一季度绩效考核')
insert into b_file values('2','半年绩效考核')

;with cte as
(
select a.u_name,b.kpi1,b.kpi2,c.t_name from b_user a
left join b_kpi b on a.u_id=b.u_id and t_id='1'
left join b_file c on b.t_id=c.t_id
)
select u_name 姓名,
convert(numeric(18,1),isnull(avg(convert(float,kpi1)),0))'绩效分数1(kpi1)',
convert(numeric(18,1),isnull(avg(convert(float,kpi2)),0))'绩效分数1(kpi2)',
isnull(t_name,'一季度绩效考核')'绩效考核项目' from cte 
group by u_name,t_name


姓名                                                 绩效分数1(kpi1)                             绩效分数1(kpi2)                             绩效考核项目
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------------------
王二                                                 0.0                                     0.0                                     一季度绩效考核
小明                                                 0.0                                     0.0                                     一季度绩效考核
李四                                                 39.0                                    39.0                                    一季度绩效考核
麻子                                                 45.7                                    47.0                                    一季度绩效考核
张三                                                 38.0                                    40.0                                    一季度绩效考核


(5 行受影响)

[棉花棒棒糖]

之前的语句少了b_file 表的链接 select u_name,isnull (kpi1,0)kpi1,isnull (kpi2,0)kpi2,'一季度绩效考核' t_id from( select a.u_id, a.u_name, avg (cast(isnull (b.kpi1,0)as real)) kpi1,avg(cast (isnull (b.kpi2,0)as real))kpi2,isnull (c.t_id,'')t_id from b_user a left join b_kpi b on a.u_id=b.u_id and b.t_id ='1' left join b_file c on b.t_id =c.t_id and c.t_id ='1' group by a.u_id, a.u_name,c.t_id)b ======================== 张三 38 40 一季度绩效考核 李四 39 39 一季度绩效考核 王二 0 0 一季度绩效考核 麻子 45.6666666666667 47 一季度绩效考核 小明 0 0 一季度绩效考核

[火拼阿三]

create table b_user(u_id nvarchar(50),u_name nvarchar(50), u_dept nvarchar(50))
insert into b_user values('1','张三','1')
insert into b_user values('2','李四','1')
insert into b_user values('3','王二','1')
insert into b_user values('4','麻子','1')
insert into b_user values('5','小明','1')
create table b_kpi(id nvarchar(50),u_id nvarchar(50), kpi1 nvarchar(50),kpi2 nvarchar(50),t_id nvarchar(50))
insert into b_kpi values('1','4','48','50','1')
insert into b_kpi values('2','4','44','49','1')
insert into b_kpi values('3','4','45','42','1')
insert into b_kpi values('4','1','38','40','1')
insert into b_kpi values('5','2','39','39','1')
insert into b_kpi values('6','4','40','38','2')
create table b_file(t_id nvarchar(50),t_name nvarchar(50))
insert into b_file values('1','一季度绩效考核')
insert into b_file values('2','半年绩效考核')

select * from (
select a.u_name,ISNULL(AVG(cast(b.kpi1 as int)),0) as kpi1,ISNULL(AVG(cast(b.kpi2 as int)),0) as kpi2,ISNULL(c.t_name,'一季度绩效考核') as t_name from b_user a
left join b_kpi b on  a.u_id=b.u_id
left join  b_file c on b.t_id=c.t_id
group by  a.u_name,c.t_name 
) d where d.t_name<>'半年绩效考核' 

[火拼阿三]

select * from (
select a.u_name,ISNULL(round(AVG(cast(b.kpi1 as float)),1),0) as kpi1,ISNULL(round(AVG(cast(b.kpi2 as float)),1),0) as kpi2,ISNULL(c.t_name,'一季度绩效考核') as t_name from b_user a
left join b_kpi b on  a.u_id=b.u_id
left join  b_file c on b.t_id=c.t_id
group by  a.u_name,c.t_name 
) d where d.t_name<>'半年绩效考核' 

修改了一下保留小数点