每个部门绩效成绩第二名 sql server 查询 （ 替代 not in ）

原题：

集团中有多个部门，部门底下有多个员工，求每个部门绩效分数排名第二的人员，数据表结构如下：

　　DEPAR          NAME             SCORE

A                   A1                  66

A                   A2                  80

A                   A3                  55

B                   B3                  36

B                   B3                  78

C                   C3                  57

C                   C3                  92

这是某公司笔试题，朋友问我的时候，我觉得挺好玩，然后直接就顺着思路写出来答案，

首先把各部门第一名排除掉，那么在求业绩的max就是每个部门的第二名，sql如下：

SELECT MAX([SCORE]) AS DSECOND,DEPAR FROM [DEPARSCORE]
 WHERE [SCORE] NOT IN
(
(SELECT MAX([SCORE])
  FROM [DEPARSCORE] group by depar)
)
GROUP BY DEPAR

查询结果，ok，棒极了

可是朋友说好像有哪里不对，我又试了试把数据改成了这样

再次查询试试

那么B的第二名去哪里了，这条sql确实是有bug的，但是错在哪里了，

我分析了一下，我觉得是 WHERE [SCORE] NOT IN 出的问题，

首先查询各部门第一名结果如下：

A80，B90，C20，然后SCORE NOT IN 得到的应该是

A66，A55，C20

问题就出来了，B部门的第二名去哪了呢，原因是SCORE NOT IN （80,90,20），那B部门的第二名当然就出不来了啊。

因为B的第二名分数正好等于A的第一名的分数，我们判断分数不等于A的第一名，那同时也不等于B的第二名。

所以说单纯的判断分数不等于之外还要加上部门判断，那怎么判断呢：

我决定这样

SELECT MAX([SCORE]) AS DSECOND,DEPAR FROM [DEPARSCORE]
 WHERE [SCORE] NOT IN
(
(SELECT MAX([SCORE],DEPAR)
  FROM [DEPARSCORE] group by depar) AS B
) AND [DEPARSCORE].DEPAR=B.DEPAR
GROUP BY DEPAR

哈哈，很明显不对，因为not in不可能跟着两个字段啊

消息 116，级别 16，状态 1，第 6 行
当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。

我很头疼，如果不用not in ，我想不出来如何做，试着百度了一下not in 发现一片文章可以替代not in，

我就试试

http://blog.****.net/shenyisyn/article/details/544694

于是就有了这段sql

select aa.*,bb.DSECOND as tempcolum from
(SELECT [SCORE],DEPAR FROM [DEPARSCORE]) as aa
 left join (SELECT MAX([SCORE]) AS DSECOND,DEPAR
  FROM [membdatabases_bak].[dbo].[DEPARSCORE] group by depar )as bb on aa.[SCORE]=bb.DSECOND
  and aa.DEPAR=bb.DEPAR

结果如下

这时候可以看出来，除了第一名之外的所有列tempcolum都为null

然后以tempcolum is null为条件查出来

得到了各部门除第一名之外的所有数据

然后

select MAX(SCORE),DEPAR from (
select aa.*,bb.DSECOND as tempcolum from
(SELECT [SCORE],DEPAR FROM [DEPARSCORE]) as aa
 left join (SELECT MAX([SCORE]) AS DSECOND,DEPAR
  FROM [membdatabases_bak].[dbo].[DEPARSCORE] group by depar )as bb on aa.[SCORE]=bb.DSECOND
  and aa.DEPAR=bb.DEPAR) as dd where tempcolum is null

  GROUP BY DEPAR

然后就成功了

好嗨森，哈哈，今天没辜负。哈哈

这种替代not in 的方法可以好好记住！！