一道月薪8000元的外企的面试题，写一个SQL语句！

有三张表：
    选手信息表（memberinfo） 字段有：ID(编号), number（报名号），name（姓名） 用户名密码等其它字段省略
    评审信息表（passinfo）   字段有：passid(评审编号),name（姓名）用户名密码等其它字段省略 （注意：评审是由系统管理员指定的，有7人）  
    评审打分信息表（passmarkinfo） 字段有：number(选手报名号)， detail_1(细节一的评审打分值)，detail_2（细节二...） detail_3（细节三...）
detail_4（细节四...） detail_5（细节五...） passid(打分的评审编号)
    这是今天下午我的一个朋友去一家外企公司面试的一道题，很遗憾他没有做出来也失去了录用的机会，我把这道题拿出来让大家讨论一下：题目的背景大概就是一个关于评审网上给选手打分的，设计到的表上面已经写的很清楚了。题目的问题是：写一个SQL语句来返回一下字段：number（选手报名号），name（选手姓名），mark（平均分） 要求是：mark（平均分）是7个评审打分值的平均，当且仅当7个评审全部打完分以后，mark字段才显示该选手的平均得分，否则，mark字段的值将显示“0”;

126 个解决方案

#1

其他字段直接关联，平均分就是isnull((detail_1+detail_2+...+detail7)/7,0)

#2

评审信息表应该没有用

#3

这 8000 元啊 。。能不能喊我去 :)

mark 是 5个细节分的和呢 还是平均值呢？
我想是和吧？

#4

select PM.number, name, mark
from 
  (select number,case when count(*)=7 then sum(detail_1+...+detail_5) / 7.0 else 0 end as mark
  from passmarkinfo
  group by number) PM left join memberinfo MB on PM.number=MB.number

已知评审员不会重复打分。

#5

给个 oracle 版的 看看对不对

--memberinfo number  name
--passinfo passid name
--passmarkinfo number detail_1  passid

Select x.Number, x.Name, Deocde(z.Pcount, 7, y.Summark / 7, 0) As Mark
From   Memberinfo x,
       (Select Number, Sum(Detail_1 + Detail_2 +.. + Detail_5) As Summark
        From   Passmarkinfo
        Group  By Number) y,
       (Select a.Number, Count(Distinct Passid) Pcount
        From   Memberinfo a, Passmarkinfo c
        Where  a.Number = c.Number
        Group  By a.Number) z
Where  x.Number = y.Number
       And x.Nummber = z.Number

#6

引用 1 楼 jinjazz 的回复:

其他字段直接关联，平均分就是isnull((detail_1+detail_2+...+detail7)/7,0)

这个明显不行啊！人家会出这么弱智的题目吗？你要注意到detail1，detail2，detail3，detail4，detail5不是代表的评审，而是选手的得分细节，比如：一个演讲选手，评审要对他的表达，举止，形象等细节打分，这些细节的总分才是评审对该选手的打分

#7

声明一下：评审不会重复打分，detail1，detail2，detail3，detail4，detail5不是代表的评审，而是选手的得分细节，比如：一个演讲选手，评审要对他的表达，举止，形象等细节打分，这些细节的总分才是评审对该选手的最终打分。而mark又是7个评审最终打分的平均分，我想我应该说的够清楚了吧！怎么还会有人误解题意呢？

#8

引用 7 楼 tang_fu 的回复:

声明一下：评审不会重复打分，detail1，detail2，detail3，detail4，detail5不是代表的评审，而是选手的得分细节，比如：一个演讲选手，评审要对他的表达，举止，形象等细节打分，这些细节的总分才是评审对该选手的最终打分。而mark又是7个评审最终打分的平均分，我想我应该说的够清楚了吧！怎么还会有人误解题意呢？

那用left jion ，avg，group by  

#9

顶一下

#10

来学习一下...Up

#11

学习

#12

帮顶一下哦

#13

学习啦~

#14

对这块不熟，过来看看。
做个标记，呵呵

#15

学习

#16

该回复于2009-06-18 17:35:40被版主删除

#17

学习了

#18

SELECT number, name, CASE WHEN MAX(TotalSource) 
      < 0 THEN 0 ELSE MAX(TotalSource) END AS mark
FROM (SELECT memberinfo.number, memberinfo.name, isnull(detail_1, - 99999) 
              + isnull(detail_2, - 99999) + isnull(detail_3, - 99999) + isnull(detail_4, - 99999) 
              + isnull(detail_5, - 99999) TotalSource
        FROM passmarkinfo LEFT JOIN
              memberinfo ON passmarkinfo.number = memberinfo.id) 
      MemberTotalSource
GROUP BY number, name

#19

一个地方写错了，是
memberinfo ON passmarkinfo.number = memberinfo.number

#20

oracle版的,这个可以!

select m.number,m.name,nvl(sum(p.detail_1+p.detail_2+p.detail_3+p.detail_4+p.detail_5+p.detail_6+p.detail_7),0)
from memberinfo m,passmarkinfo p
where m.number=p.number(+)
group by m.number,m.name

#21

引用 8 楼 jinjazz 的回复:

引用 7 楼 tang_fu 的回复:
声明一下：评审不会重复打分，detail1，detail2，detail3，detail4，detail5不是代表的评审，而是选手的得分细节，比如：一个演讲选手，评审要对他的表达，举止，形象等细节打分，这些细节的总分才是评审对该选手的最终打分。而mark又是7个评审最终打分的平均分，我想我应该说的够清楚了吧！怎么还会有人误解题意呢？ 
 

那用left jion ，avg，group by  

并判断一下各个评审分是否为0再做后面的就行了

#22

不好意思忘了处以7了.加上除以7

#23

关注.

#24

8000了还有笔试。。。呵呵~~

#25

学习一下，等待高人

#26

学习ING

#27

如果打分的记录数7条，那么就查询平均分。否则Select 0

#28

引用 18 楼 wartim 的回复:

SQL codeSELECT number, name, CASE WHEN MAX(TotalSource) 
      < 0 THEN 0 ELSE MAX(TotalSource) END AS mark
FROM (SELECT memberinfo.number, memberinfo.name, isnull(detail_1, - 99999) 
              + isnull(detail_2, - 99999) + isnull(detail_3, - 99999) + isnull(detail_4, - 99999) 
              + isnull(detail_5, - 99999) TotalSource
        FROM passmarkinfo LEFT JOIN
              membe…

支持

#29

外企？
首先我的English都不过关  - -
遗憾啊··············

#30

引用 20 楼 pingpingdong 的回复:

oracle版的,这个可以! 

SQL codeselect m.number,m.name,nvl(sum(p.detail_1+p.detail_2+p.detail_3+p.detail_4+p.detail_5+p.detail_6+p.detail_7),0)
from memberinfo m,passmarkinfo p
where m.number=p.number(+)
group by m.number,m.name

好像可以呢

#31

CREATE TABLE #tmpTable
(
	id int identity(1,1) primary key,
	userCode nvarchar(20) not null,
	goal1 numeric(18,2),
	goal2 numeric(18,2),
	goal3 numeric(18,2),
	goal4 numeric(18,2),
	goal5 numeric(18,2),
	auditCode int
)

INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,1)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,2)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,3)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,4)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,5)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,6)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,7)
INSERT INTO #tmpTable VAlues('B',53.15,15.15,14.58,16.85,16.85,5)
SELECT userCode,(CASE COUNT(*) when 7 THEN SUM(goal1+goal2+goal3+goal4+goal5)/7 ELSE 0 END) AS Goal  FROM #tmpTable
GROUP BY userCode
DROP TABLE #tmpTable

#32

Select a.[Number],b.[Name],Case(PCount) When 7 Then a.Mark else 0 End
From
(
SELECT Number,
       SUM(ISNULL(detail_1,0)+ISNULL(detal_2,0)+.....+ISNULL(detail_7,0)) AS Mark,
Count(Number) as PCount
From passinfo
Group By Number) a 
LEFT JOIN 
memberinfo b
ON
a.Number=b.Number

#33

输出：
userCode Goal
a         116.580000
b         0

#34

帮顶，学习了。

#35

Select a.[Number],b.[Name],Case(PCount) When 7 Then a.Mark/7.0 else 0 End
From
(
SELECT Number,
       SUM(ISNULL(detail_1,0)+ISNULL(detal_2,0)+.....+ISNULL(detail_7,0)) AS Mark,
ISNULL(Count(Number),0) as PCount
From passinfo
Group By Number) a 
LEFT JOIN 
memberinfo b
ON
a.Number=b.Number

#36

学习

#37

评审不会重复打分：
select a.number, a.name, decode(b.smark, null, 0, b.smark/7) mark
from memberinfo a,
    (select x.number, sum(x.detail_1 + x.detail_2 + x.detail_3 + x.detail_4 + x.detail_5) smark
     from passmarkinfo x, passinfo y
     where x.passid=y.passid
     group by x.number 
     having count(distinct a.passid) = 7
    ) b
where a.number = b.number(+)

#38

MARK下，看看有没有更好的答案

#39

up一下 哈哈！

#40

有个问题

1.所谓的平均分是,先求,每个评委的每个细节平均分,然后把七个评委加起来再平均
还是,先求一个评委的,所有细节总分,然后求,七个评委的平均总分


做法

评委表 无条件的 JOIN 选手表 得到 结果 A(选手报名号,选手姓名,评委编号)
SELEC * INTO A FROM memberinfo, passinfo

然后

SELECT A.*,SUM(B.detail_1+...+B.detail_5) As Core INTO C FROM A LEFT JOIN 评审打分信息表 B ON A.选手报名号=B.选手报名号 AND A.评委编号=B.评委编号
得到的是一个所有的评委,对所有选手的评分,如果某个评委没有对选手评分的话, Core就为NULL了

最终要的结果就是

SELECT number, name, AVG(Core/7) as mark FROM C  得到的所有评委的细节平均分
SELECT number, name, AVG(Core) as mark FROM C  得到的所有评委的细节总分平均分



所以最终代码

SELECT
	A.Number,
	A.Name,
	AVG(Mark / 7) as '所有细节平均分',
	AVG(Mark) as '所有细节总分的平均分'
FROM
(
	SELECT
		A.Number,
		A.Name,
		A.Passid,
		SUM(
			ISNULL(B.detail_1,0) + 
			ISNULL(B.detail_2,0) + 
			ISNULL(B.detail_3,0) + 
			ISNULL(B.detail_4,0) + 
			ISNULL(B.detail_5,0)
		) as Mark
	FROM
		(SELECT memberinfo.Number, memberinfo.Name, passinfo.Passid FROM memberinfo, passinfo) A
	LEFT JOIN 
		passmarkinfo B
	ON A.number=B.Number AND A.Passid=B.Passid
) A
GROUP BY Number, [Name]

#41

引用 31 楼 cj205 的回复:

SQL code
CREATE TABLE #tmpTable
(
    id int identity(1,1) primary key,
    userCode nvarchar(20) not null,
    goal1 numeric(18,2),
    goal2 numeric(18,2),
    goal3 numeric(18,2),
    goal4 numeric(18,2),
    goal5 numeric(18,2),
    auditCode int
)

INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,1)
INSERT INTO #tmpTable VAlues('a',53.15,15.15,14.58,16.85,16.85,2…

顶这个

#42

select a.number,a.name, b.result 
from memberinfo  a  left outer join   
(select  number  , (sum(detail_1)+ sum(detail_2))/7 as result   from passmarkinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number   

测试了下发现可以 来来回回弄了半个小时  顶下 学到了不少东西俄 
关键是下面这个表
select  number  , (sum(detail_1)+ sum(detail_2))/7 as result   from passmarkinfo group by number having  count(distinct(passid))=7
功能： 统计7个不相同的评委都打分的情况下的 平均分数 
上面的还有不足的地方  当没分的时候 result 为null
开始我想加个 isnull(  (sum(detail_1)+ sum(detail_2))/7,0)  发现还不行  分析了下 得在最终的 b.result 处加 isnull 

最终答案如下：
select a.number,a.name, isnull(b.result,0) as result
from memberinfo  a  left outer join   
(select  number  , (sum(detail_1)+ sum(detail_2))/7 as result   from passmarkinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number  
很好的例子 弄到博客去 

#43

看的时候才 到10楼啊 怎么一回复都跑到 40了 
服了

#44

不要动不动就8000

#45

装b遭雷p........  郁闷. 这样的题目也值?

#46

会做这个就有 8000 块 啊  看来我应该要求加薪了 

#47

select a.number,a.name, isnull(b.result,0) as result
from memberinfo  a  left outer join   
(select  number  , (sum(detail_1)+ sum(detail_2)+sum(detail_3)+sum(detail_4)+sum(detail_5))/7 as result   from passmarkinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number  

弄个完整的吧  

#48

八千哈哈！！

#49

UP

#50

这种题目只是企业筛选掉一些人的