具有GROUP，MIN和NULL值的SQL命令的唯一结果 [英] Unique results for SQL command with GROUP, MIN and NULL values

问题描述

我有一个表查询与列作业，性别（ TRUE 为女性， FALSE 为男性）和薪水。没有一列是唯一的， salary 可能包含 NULL 。如何查找女性（ TRUE 作业的最低工资 >）？如果例如有条目 [pilot; TRUE; 100] ， [pilot; FALSE; 100] ， [pilot; TRUE; NULL] 和 [pilot; FALSE; 120] ，下面的代码返回 [pilot; 100] 两次而不是一次。

  SELECT TOP（100）PERCENT T.JOB，T. SALARY 
 FROM INQUIRY as T INNER JOIN 
（SELECT JOB，MIN（SALARY）AS SL 
 FROM T 
 where（SALARY NOT NULL）AND（GENDER = 1） 
 GROUP BY JOB）AS x ON x.JOB = T.JOB AND x.SL = T.SALARY 
  

null s。解决方案

失去连接，你应该可以：

  SELECT job，MIN（工资）
 FROM inquiry 
 WHERE gender = 1 
 GROUP BY job 
  

I have a table inquiry with columns job, gender (TRUE for women, FALSE for men) and salary. None of the columns is unique, salary may contain NULL. How to find the minimum salary per job for women (TRUE)? If e.g. there are entries [pilot ; TRUE ; 100], [pilot ; FALSE ; 100], [pilot ; TRUE ; NULL] and [pilot ; FALSE ; 120], the code below returns [pilot ; 100] twice instead of once.

SELECT TOP (100) PERCENT T.JOB, T.SALARY
FROM INQUIRY AS T INNER JOIN
    (SELECT JOB, MIN(SALARY) AS SL
    FROM INQUIRY AS T
    WHERE (SALARY IS NOT NULL) AND (GENDER = 1)
    GROUP BY JOB) AS x ON x.JOB = T.JOB AND x.SL = T.SALARY

解决方案

Aggregate functions ignore nulls. Lose the join and you should be OK:

SELECT   job, MIN(salary)
FROM     inquiry
WHERE    gender = 1
GROUP BY job

这篇关于具有GROUP，MIN和NULL值的SQL命令的唯一结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！