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

时间:2023-03-08 17:18:54

原题:

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

  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,棒极了

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

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

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

再次查询试试

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

那么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

结果如下

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

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

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

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

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

然后

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

然后就成功了

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

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

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