求一SQL语句

Question

有一表T1:
考核名称 考核人 被考核人 考核项目 分数
优秀员工 张三   AA       考勤    24 
优秀员工 张三   BB       考勤    34     
优秀员工 张三   CC       考勤    20     
优秀员工 张三   AA       学历    45     
优秀员工 张三   BB       学历    24     
优秀员工 张三   CC       学历    12 
优秀员工 李四   AA       考勤    18 
优秀员工 李四   BB       考勤    57     
优秀员工 李四   CC       考勤    20     
优秀员工 李四   AA       学历    24     
优秀员工 李四   BB       学历    19     
优秀员工 李四   CC       学历    50 
....
查询表:
考核名称 考核人    被考核人  考核项目       总分
优秀员工 张三,李四  AA    考勤:42学历:69  111
优秀员工 张三,李四  BB    考勤:91学历:43  134
优秀员工 张三,李四  CC    考勤:40学历:62  102
...
最好,考勤:42学历:69 实现换行显示,如
考勤:42
学历:69            

Answer 1

DECLARE @t TABLE(考核名称 VARCHAR(10), 考核人 VARCHAR(10),被考核人 VARCHAR(10),考核项目 VARCHAR(10),分数 INT)
INSERT @t SELECT '优秀员工','张三','AA','考勤',    24
UNION ALL SELECT '优秀员工','张三','BB','考勤',    34   
UNION ALL SELECT '优秀员工','张三','CC','考勤'    ,20   
UNION ALL SELECT '优秀员工','张三','AA','学历'   , 45   
UNION ALL SELECT '优秀员工','张三','BB','学历'  ,  24   
UNION ALL SELECT '优秀员工','张三','CC','学历' ,   12
UNION ALL SELECT '优秀员工','李四','AA','考勤',    18
UNION ALL SELECT '优秀员工','李四','BB','考勤'    ,57   
UNION ALL SELECT '优秀员工','李四','CC','考勤'   , 20   
UNION ALL SELECT '优秀员工','李四','AA','学历'  ,  24   
UNION ALL SELECT '优秀员工','李四','BB','学历' ,   19   
UNION ALL SELECT '优秀员工','李四','CC','学历',    50 
SELECT 考核名称,v 考核人,被考核人,v1 项目,sm 总分 FROM 
(SELECT 考核名称,被考核人,SUM(分数) sm FROM @t GROUP BY 被考核人,考核名称) a
CROSS APPLY
(SELECT v=
STUFF((SELECT DISTINCT ',' + 考核人 FROM @t WHERE 被考核人=a.被考核人 FOR XML PATH(''),TYPE).value('/','VARCHAR(1000)'),1,1,'')
) b
CROSS APPLY
(
SELECT v1=
(SELECT 考核项目+':'+RTRIM(SUM(分数)) FROM @t WHERE 被考核人=a.被考核人 GROUP BY 被考核人,考核项目 FOR XML PATH(''),TYPE).value('/','VARCHAR(1000)')
) c
/*
优秀员工 李四,张三 AA 考勤:42学历:69 111
优秀员工 李四,张三 BB 考勤:91学历:43 134
优秀员工 李四,张三 CC 考勤:40学历:62 102
*/

Answer 2

if object_id('T1')is not null drop table T1
go
create table T1 (考核名称 nvarchar(10), 考核人 nvarchar(10), 被考核人 nvarchar(5), 考核项目 nvarchar(5), 分数  int)
insert T1 select N'优秀员工' ,N'张三' , 'AA'     ,N'考勤'   , 24 
insert T1 select N'优秀员工' ,N'张三' , 'BB'     , N'考勤'   , 34    
insert T1 select N'优秀员工' ,N'张三'  ,'CC'     , N'考勤'   , 20    
insert T1 select N'优秀员工' ,N'张三'  ,'AA'    ,  N'学历'  ,  45    
insert T1 select N'优秀员工' ,N'张三' , 'BB'   ,  N'学历'    ,24    
insert T1 select N'优秀员工' ,N'张三',  'CC'    , N'学历'    ,12 
insert T1 select N'优秀员工' ,N'李四' , 'AA'   ,   N'考勤'   , 18 
insert T1 select N'优秀员工' ,N'李四'  ,'BB'   ,   N'考勤'    ,57    
insert T1 select N'优秀员工' ,N'李四'  ,'CC'   ,   N'考勤'    ,20    
insert T1 select N'优秀员工' ,N'李四'  ,'AA'  ,    N'学历'   , 24    
insert T1 select N'优秀员工' ,N'李四'  ,'BB'  ,    N'学历'   , 19    
insert T1 select N'优秀员工' ,N'李四'  ,'CC'   ,   N'学历'  ,  50 
IF OBJECT_ID('DBO.F_STR')IS NOT NULL DROP FUNCTION DBO.F_STR
GO
CREATE FUNCTION dbo.f_str(@STR NVARCHAR(20))
RETURNS Nvarchar(4000)
AS
BEGIN
    DECLARE @r Nvarchar(4000)
    SET @r = ''
    SELECT @r = @r + ',' +  考核人
    FROM T1
    WHERE 被考核人=@STR GROUP BY  考核人
    RETURN STUFF(@r, 1, 1, '')
END
GO
IF OBJECT_ID('DBO.F_STR2')IS NOT NULL DROP FUNCTION DBO.F_STR2
GO
CREATE FUNCTION dbo.f_str2(@STR NVARCHAR(20))
RETURNS Nvarchar(4000)
AS
BEGIN
    DECLARE @r Nvarchar(4000)
    SET @r = ''
    SELECT @r = @r + ':' +  考核项目+':'+CAST(SUM(分数) AS VARCHAR(5))
    FROM T1
    WHERE 被考核人=@STR GROUP BY  考核项目
    RETURN STUFF(@r, 1, 1, '')
END
GO
SELECT 考核名称 ,DBO.F_STR(被考核人)考核人,被考核人,DBO.F_STR2(被考核人) 考核项目,SUM(分数)分数 FROM T1 GROUP BY  被考核人,考核名称
/*考核名称       考核人                                                                                                                                                                                                      被考核人  考核项目                                                                                                                                                                                                     分数          
---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
优秀员工       李四,张三                                                                                                                                                                                                    AA    考勤:42:学历:69                                                                                                                                                                                              111
优秀员工       李四,张三                                                                                                                                                                                                    BB    考勤:91:学历:43                                                                                                                                                                                              134
优秀员工       李四,张三                                                                                                                                                                                                    CC    考勤:40:学历:62                                                                                                                                                                                              102*/

Answer 3

參照
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

Answer 4

/*
考核名称 考核人    被考核人  考核项目      总分 
优秀员工 张三,李四  AA    考勤:42学历:69  111 
优秀员工 张三,李四  BB    考勤:91学历:43  134 
优秀员工 张三,李四  CC    考勤:40学历:62  102 
*/
create TABLE ttt(ExamName VARCHAR(10),ExamPerson VARCHAR(10),Examer VARCHAR(10),ExamProject VARCHAR(10),ExamScore INT)
INSERT ttt SELECT '优秀员工','张三','AA','考勤',24
UNION ALL SELECT '优秀员工','张三','BB','考勤',34   
UNION ALL SELECT '优秀员工','张三','CC','考勤',20   
UNION ALL SELECT '优秀员工','张三','AA','学历',45   
UNION ALL SELECT '优秀员工','张三','BB','学历',24   
UNION ALL SELECT '优秀员工','张三','CC','学历',12
UNION ALL SELECT '优秀员工','李四','AA','考勤',18
UNION ALL SELECT '优秀员工','李四','BB','考勤',57   
UNION ALL SELECT '优秀员工','李四','CC','考勤',20   
UNION ALL SELECT '优秀员工','李四','AA','学历',24   
UNION ALL SELECT '优秀员工','李四','BB','学历',19   
UNION ALL SELECT '优秀员工','李四','CC','学历',50 --drop function dbo.fc_Score 
go
create function dbo.fc_Name(@Person varchar(20))
RETURNS varchar(8000) 
as
begin
  declare @pp varchar(100)
  set @pp=''
  select @pp=@pp+','+ExamPerson from ttt where Examer=@Person group by ExamPerson
  return stuff(@pp,1,1,'')
end
go
select ExamName,ExamPerson=dbo.fc_Name(Examer),Examer,ExamProject,ExamScore from tttcreate function dbo.fc_Score(@Person varchar(20))
RETURNS varchar(8000) 
as
begin
  declare @Project varchar(100)
  set @Project=''
  select @Project=@Project+':'+ExamProject+':'+(cast(sum(ExamScore) as varchar)) from ttt where Examer=@Person group by ExamProject
  return stuff(@Project,1,1,'')
endselect ExamName,ExamPerson=dbo.fc_Name(Examer),Examer,ExamProject=dbo.fc_Score(Examer),sum(ExamScore) '总分' from ttt group by ExamName,Examer

Answer 5

sql2005语法。类似于inner join ,不过on条件写到里面去了。sql2000不支持