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

Question

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

Answer 1

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

Answer 2

select PM.number, name, 
from 
  (select number,case when count(*)=7 then sum(detail_1+...+detail_5) / 7.0 else 0 end as 
  from passinfo
  group by number) PM left join memberinfo MB on PM.number=MB.number已知评审员不会重复打分。

Answer 3

给个 oracle 版的 看看对不对--memberinfo number  name
--passinfo passid name
--passinfo number detail_1  passidSelect x.Number, x.Name, Deocde(z.Pcount, 7, y.Sum / 7, 0) As Mark
From   Memberinfo x,
       (Select Number, Sum(Detail_1 + Detail_2 +.. + Detail_5) As Sum
        From   Passinfo
        Group  By Number) y,
       (Select a.Number, Count(Distinct Passid) Pcount
        From   Memberinfo a, Passinfo c
        Where  a.Number = c.Number
        Group  By a.Number) z
Where  x.Number = y.Number
       And x.Nummber = z.Number

Answer 4

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

Answer 5

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

Answer 6

那用left jion ，avg，group by  

Answer 7

SELECT number, name, CASE WHEN MAX(TotalSource) 
      < 0 THEN 0 ELSE MAX(TotalSource) END AS 
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 passinfo LEFT JOIN
              memberinfo ON passinfo.number = memberinfo.id) 
      MemberTotalSource
GROUP BY number, name

Answer 8

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

Answer 9

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,passinfo p
where m.number=p.number(+)
group by m.number,m.name

Answer 10

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

Answer 11

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

Answer 12

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

Answer 13

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

Answer 14

输出：
userCode Goal
a         116.580000
b         0

Answer 15

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

Answer 16

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

Answer 17

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

Answer 18

有个问题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  FROM C  得到的所有评委的细节平均分
SELECT number, name, AVG(Core) as  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 
passinfo B
ON A.number=B.Number AND A.Passid=B.Passid
) A
GROUP BY Number, [Name]

Answer 19

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 passinfo 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 passinfo 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 passinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number  
很好的例子 弄到博客去 

Answer 20

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 passinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number  弄个完整的吧  

Answer 21

稍微改一下
SELECT
    A.Number,
    A.Name,
    CASE COUNT(Mark) WHEN 7 THEN AVG(Mark / 7) ELSE 0 END AS '所有细节平均分',
    CASE COUNT(Mark) WHEN 7 THEN AVG(Mark) ELSE 0 END 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 
        passinfo B
    ON A.number=B.Number AND A.Passid=B.Passid
) C -- A 表左连接 评分表, 有评分的取评分,没评分的取NULL, 
GROUP BY Number, [Name]-- 最终得到就是最终的平均分了

Answer 22

写错了,再改一下SELECT
    A.Number,
    A.Name,
    CASE COUNT(Mark) WHEN 7 THEN AVG(Mark / 7) ELSE 0 END AS '所有细节平均分',
    CASE COUNT(Mark) WHEN 7 THEN AVG(Mark) ELSE 0 END AS '所有细节总分的平均分'
FROM
(
    SELECT
        A.Number,
        A.Name,
        A.Passid,
        SUM(
            B.detail_1 + 
            B.detail_2 + 
            B.detail_3 + 
            B.detail_4 + 
            B.detail_5
        ) as Mark
    FROM
        (SELECT memberinfo.Number, memberinfo.Name, passinfo.Passid FROM memberinfo, passinfo) A  -- 产生一个所有选手的对应所有评委的评分表,但没有评分数据
    LEFT JOIN 
        passinfo B
    ON A.number=B.Number AND A.Passid=B.Passid
) C            -- A 表左连接 评分表, 有评分的取评分,没评分的取NULL, 
GROUP BY Number, [Name]-- 最终得到就是最终的平均分了

Answer 23

select a.number,a.name, isnull(b.result,0) as result
from memberinfo  a  left outer join   
(select  number  ,avg(detail_1)+ avg(detail_2)+avg(detail_3)+avg(detail_4)+avg(detail_5) as result   from passinfo group by number having  count(distinct(passid))=7 ) as b
on b.number=a.number  再改进下用 Avg 算了  /7太土了 

Answer 24

select passinfo.number ,memberinfo.[name], (select @sum=isnull(sum(detial1+detial2...)/7,0)
    from passinfo group by number ) as  from passinfo
    inner join memberinfo on passinfo.number = memberinfo.number

Answer 25

create table memberinfo
(
ID int identity(1,1),
 number int
)
create table passinfo
(
passid int identity(1,1),
[name] nvarchar(20)
)create table passinfo
(
   number int,
   passid int,
   detail_1 int,
   detail_2  int
)sql 语句：
select number, 
(select  case when count(*)= (select count(*) from passinfo)  
              then sum(detail_1+detail_2)/2 else 0  end  as  
 from passinfo   where number=memberinfo.ID 
)  as from  memberinfo

Answer 26

会做这个8K题的,也许连简单的CSS都搞不定哈

Answer 27

T-SQL
假设满分为100分，7位专家就为700分，那么我可以用下面这个方法select case avg(isnull(detail_1 + detail_2 + detail_3...+detail_7,-701)) < 0 then 0 else avg(isnull(detail_1 + detail_2 + detail_3...+detail_7,-701)) from ...

Answer 28

T-SQL 
假设满分为100分，7位专家就为700分，那么我可以用下面这个方法 select case avg(isnull(detail_1 + detail_2 + detail_3...+detail_7,-701)) < 0 then 0 else avg(isnull(detail_1 + detail_2 + detail_3...+detail_7,-701)) end from ...上面少个end

Answer 29

create table memberinfo 
(
[id] int primary key,
[number] varchar(20) not null,
[name] varchar(20) not null
)
gocreate table passinfo
(
[passid] int primary key,
[number] varchar(20) not null,
)
gocreate table passinfo
(
number varchar(20) not null,
detail_1 decimal(5,2),
detail_2 decimal(5,2),
detail_3 decimal(5,2),
detail_4 decimal(5,2),
detail_5 decimal(5,2),
passid int
)
goinsert into memberinfo values(1,'001','aaa')
insert into memberinfo values(2,'002','bbb')
insert into memberinfo values(3,'003','ccc')
insert into memberinfo values(4,'004','ddd')insert into passinfo values(1,'zzz')
insert into passinfo values(2,'yyy')
insert into passinfo values(3,'xxx')insert into passinfo values('001',2,3,4,5,6,1) 
insert into passinfo values('002',2,1,4,5,6,2)
insert into passinfo values('002',2,1,4,5,6,3)
insert into passinfo values('001',2,3,4,5,6,3)
insert into passinfo values('003',2,3,4,2,6,1)
insert into passinfo values('003',2,3,4,3,6,2)
insert into passinfo values('001',2,3,4,4,6,2)select distinct(a.number),b.name,convert(decimal(5,2),
case when p.num<=2 then 0 else p.tol/p.num end) as memavg 
from memberinfo b inner join passinfo a on b.number = a.number left join 
(select number, count(passid) as num,sum(detail_1+detail_2+detail_3+detail_4+detail_5) as tol from passinfo group by number) p
on a.number = p.number 等更好的方法

Answer 30

先创建一个函数
CREATE FUNCTION GetAvg(@Number char(10))
RETURNS INT
AS
BEGIN
 DECLARE @avg INT;
 SELECT @avg=(sum(detail_1)+sum(detail_2)+sum(detail_3)+sum(detail_4)+sum(detail_5))/7 from [3Table] GROUP BY NUMBER having Number=1
 RETURN @avg;
END
GO查询语句
SELECT memberinfo.number,Sname,isNull(avgMark,0) as Mark FROM memberinfo LEFT JOIN (
SELECT number, CASE count(passid) WHEN 7 THEN dbo.GetAvg(Number) ELSE 0
END AS avgMark from passinfo GROUP BY number )AS T1  ON T1.number=memberinfo.number

Answer 31

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

Answer 32

就是表的交叉
select a.number, a.name, decode(b.s, null, 0, b.s/7)  
from memberinfo a, 
    (select x.number, sum(x.detail_1 + x.detail_2 + x.detail_3 + x.detail_4 + x.detail_5) s 
    from passinfo x, passinfo y 
    where x.passid=y.passid 
    group by x.number 
    having count(distinct a.passid) = 7 
    ) b 
where a.number = b.number(+)

Answer 33

select 
  m.number,m.name,decode(p.detail,null,0,p.detail) 
from
memberinfo m,
(
  select d.number,d.sumpassid,d.detail / sumpassid 
  from
  (
    select 
      number,
      sum(passid) sumpassid,
      sum(detail_1 + detail_2 + detail_3 + detail_4 + detail_5 detail),
      from passinfo
    group by number
  ) d
    where d.sumpassid = (
                         select sum(*)
                         from passinfo
                        )
) p
where m.number = p.number(+)上述代码不限于评论人数，没有在sql中测试，但是方法绝对可行，若有错误自己做些调整即可。

Answer 34

select a.number,a.name,(case when c.mycount1 = d.tcount then detail/d.tcount  else 0 end) as avg
from memberinfo a
left join 
(
select number,sum(detail_1 + detail_2+ detail_3+detail_4+detail_5) detail from passinfo group by number
) b on b.number = a.number
left join 
( select passid,count(distinct passid) as mycount1 from passinfo on passinfo.number = a.number group by passid  ) c
left join
( select count(*) as tcount from passinfo) d

Answer 35

select 
  m.number,m.name,decode(pm.detail,null,0,pm.detail) 
from
memberinfo m,
(
  select d.number,d.sumpassid,d.detail / sumpassid detail
  from
  (
    select 
      number,
      count(passid) sumpassid,
      sum(detail_1 + detail_2 + detail_3 + detail_4 + detail_5) detail
      from passinfo
      group by number
  ) d
  where d.sumpassid = (
                        select count(*)
                        from passinfo
                      )
) pm
where m.number = pm.number(+)
整理了一下刚才的语句，第6行到第15行语句测试通过（DB:sql server 2005中），sql server中decode出错，无法验证全语句。

Answer 36

sql server下可用case...when代替。

Answer 37

select case when min(sum)=0 then 0 else avg(sum) end 
from (
 select case when detail_1=0 or detail_2=0 or detail_3=0 or detail_4=0 then 0
        else detail_1+detail_2+detail_3+detail_4 end sum 
 from passinfo a,passinfo b,memberinfo c
where a.passid=b.passid and a.number=c.number 
)a