如何显示group by 以外的列
有一个成绩表,表结构如下
1IDint40
0ExamineeNamenvarchar500 姓名
0PositionNumnvarchar501 职位
0Departmentnvarchar501 应聘单位
0ExamRoomNumint41 考场号
0ExamXhint41 序号
0InterviewScoredecimal91 成绩
我用
select examroomnum,positionnum,count(*) as 人数, sum(interviewscore),avg(interviewscore) from examinee group by examroomnum,positionnum
显示结果如下
考场号 职位代码 总分 平均分
1101002001156.0056.000000
11040010012186.0093.000000
12010050024233.8058.450000
332121920015363.8072.760000
我想在显示结果里面显示 应聘单位列,不知道如何实现.(不将应聘单位加到group by 里面)
if exists (select * from sysobjects where id = OBJECT_ID('[Examinee]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [Examinee]
CREATE TABLE [Examinee] ( [ID] [int] NOT NULL , [ExamineeName] [nvarchar] (50) NOT NULL , [PositionNum] [nvarchar] (50) NULL , [Department] [nvarchar] (50) NULL , [ExamRoomNum] [int] NULL , [ExamXh] [int] NULL , [InterviewScore] [smallmoney] NULL )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 2 , '贺成' , '104001001' , '市财政局票据管理中心102' , 1 , 5 , 88.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 3 , '熊峰' , '104001001' , '市财政局票据管理中心101' , 1 , 6 , 98.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 6 , '练荣' , '101002001' , '市法学会201' , 1 , 7 , 56.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 15 , '廖典' , '201005002' , '市经济信息中心502' , 1 , 1 , 87.80 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 16 , '余宏华' , '201005002' , '市经济信息中心502' , 1 , 2 , 67.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 17 , '余来辉' , '201005002' , '市经济信息中心502' , 1 , 3 , 45.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 18 , '胡秋云' , '201005002' , '市经济信息中心502' , 1 , 4 , 34.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 943 , '艾福娇' , '212192001' , '西湖区保育院19201' , 33 , 1 , 87.80 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 946 , '敖魏' , '212192001' , '西湖区保育院19201' , 33 , 3 , 56.70 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 947 , '陈露' , '212192001' , '西湖区保育院19201' , 33 , 4 , 67.00 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 969 , '安丰洁' , '212192001' , '西湖区保育院19201' , 33 , 2 , 84.40 )
INSERT [Examinee] ( [ID] , [ExamineeName] , [PositionNum] , [Department] , [ExamRoomNum] , [ExamXh] , [InterviewScore] ) VALUES ( 975 , '单欣' , '212192001' , '西湖区保育院19201' , 33 , 5 , 67.90 )
SELECT a.examroomnum ,
a.positionnum ,
a.人数 ,
a.suminterviewscore ,
a.avginterviewscore ,
b.Department
FROM ( SELECT examroomnum ,
positionnum ,
COUNT(*) AS 人数 ,
SUM(interviewscore) suminterviewscore ,
AVG(interviewscore) avginterviewscore
FROM examinee
GROUP BY examroomnum ,
positionnum
) a
LEFT JOIN examinee b ON a.examroomnum = b.examroomnum
AND a.positionnum = b.positionnum
/*
examroomnum positionnum 人数 suminterviewscore avginterviewscore Department
----------- -------------------------------------------------- ----------- --------------------- --------------------- --------------------------------------------------
1 101002001 1 56.00 56.00 市法学会201
1 104001001 2 186.00 93.00 市财政局票据管理中心102
1 104001001 2 186.00 93.00 市财政局票据管理中心101
1 201005002 4 233.80 58.45 市经济信息中心502
1 201005002 4 233.80 58.45 市经济信息中心502
1 201005002 4 233.80 58.45 市经济信息中心502
1 201005002 4 233.80 58.45 市经济信息中心502
33 212192001 5 363.80 72.76 西湖区保育院19201
33 212192001 5 363.80 72.76 西湖区保育院19201
33 212192001 5 363.80 72.76 西湖区保育院19201
33 212192001 5 363.80 72.76 西湖区保育院19201
33 212192001 5 363.80 72.76 西湖区保育院19201
(12 行受影响)
*/
:with cte as
(
select examroomnum,positionnum,count(*) as 人数, sum(interviewscore),avg(interviewscore) from examinee group by examroomnum,positionnum
)
select t.examroomnum ,t.positionnum ,t.人数,t.suminterviewscore,
t.avginterviewscore,b.departmentfrom cte as t
left join examinee as b
on t.examroomnum = b.examroomnum
and t.positionnum = b.positionnum
:with cte as
(
select examroomnum,positionnum,count(*) as 人数, sum(interviewscore),avg(interviewscore) from examinee group by examroomnum,positionnum
)
select t.examroomnum ,t.positionnum ,t.人数,t.suminterviewscore,
t.avginterviewscore,b.departmentfrom
from cte as t
left join examinee as b
on t.examroomnum = b.examroomnum
and t.positionnum = b.positionnum