使用Groupby选择语句，同时忽略Postgresql上的列

Question

我有一个表名"报告"；在Postgresql数据库中类似：

Student Class Marks Observation_time
A        1     11    21/7/2020
A        2     13    18/7/2020   
B        1     19    17/7/2020
A        1     17    15/7/2020
B        1     15    21/7/2020
C        1     NAN     10/7/2015
C        1     NAN     11/7/2015
C        2     8     10/7/2015
C        2     0     11/7/2015
D        1     NAN     10/7/2015
D        1     NAN     11/7/2015
D        2     NAN     10/7/2015
D        2     NAN     11/7/2015

我想从上表中获得特定学生和班级的所有分数始终为NAN(无论观察时间如何(的行。

预期输出为：

student class
C       1
D       1
D       2

有人能帮我查询一下吗？感谢

Answer 1

我为您的问题创建了一个示例。

CREATE TABLE reportt (
  Class  int,
  Marks  int,
  Student  VARCHAR(100),
  Observation_time VARCHAR(100),
);

INSERT INTO reportt
  (Student, Class, Marks,Observation_time)
VALUES
  ('A',1,11,'21/7/2020'),
  ('A',2,13,'18/7/2020'),
  ('B',1,19,'17/7/2020'),
  ('A',1,17,'15/7/2020'),
  ('B',1,15,'21/7/2020'),
  ('C',1,null,'10/7/2015'),
  ('C',1,null,'11/7/2015'),
  ('C',2,8,'10/7/2015'),
  ('C',2,0,'11/7/2015'),
  ('D',1,null,'10/7/2015'),
  ('D',1,null,'11/7/2015'),
  ('D',2,null,'10/7/2015'),
  ('D',2,null,'11/7/2015')
  ;

  with CTE_select as (
    select ISNULL(Marks,0) Marks, Student,Class 
    from  reportt 
  )
  select Student,Class,SUM(Marks) from CTE_select
  where marks >= 0
  group by Class,Student
  having   SUM(Marks)= 0;

结果=

Student Class
C        1  
D        1  
D        2  

Answer 2

如果您想在Marks中查找所有带有NULL的行，请使用：

SELECT DISTINCT Student,Class
FROM report
WHERE Marks IS NULL;

DISTINCT操作符从结果中删除重复项

另一种变体是：

SELECT Student,Class
FROM report
GROUP BY Student,Class
HAVING COUNT(*)=COUNT(*)FILTER(WHERE Marks IS NULL)

Answer 3

查看有关GROUP BY的文档，它非常强大，但也可能相当棘手。前面的答案(DISTINCT(实际上也是一种GROUP BY。我认为这应该会给你带来你想要的结果，但请阅读文档以了解发生了什么。

Select MIN(Student), MIN(Class)
  from report
where Marks = 0
  group by Student, Class