连接两个sql表的结果

Question

我想从按相同规则分组的两个表中获取结果，这些表在一​​个选择中连接在一起。

我有表 1

create table person AS 
id INTEGER,
gender INTEGER,
state VARCHAR2
name VARCHAR2
surname VARCHAR2

表 2

create table sampletest as
person_id FOREIGN KEY To person.id
result INTEGER

表 3

create table examtest as 
person_id FOREIGN KEY to person.id
examresult INTEGER

我想

按状态 | 获取此输出组按性别分组 |计数(考试结果>0) | count(result>0 and result<4)

我尝试了类似的方法

select state,gender,count(e.examresult),count(s.result) where 
p.id=s.person_id and p.id=e.person_id and 
s.result>0 and s.result<4 and 
e.examresult>0 group by state,gender

，但得到的结果相互依赖。如何将独立结果纳入一项选择？

Answer 1

  SELECT state,gender,
         SUM(CASE WHEN e.examresult > 0 THEN 1 ELSE 0 END) AS EXAM_GT_ZERO,
         SUM(CASE WHEN s.result BETWEEN 0 AND 4 THEN 1 ELSE 0 END) AS SMPL_0_to_4
    FROM person p
         LEFT JOIN sampletest s
         ON p.id = s.person_id 
         LEFT JOIN examtest e
         ON p.id = e.person_id
GROUP BY state,gender

Answer 2

构建子选择

select
  p.state,
  p.gender,
  sum( 
    ( select count(1) from examtest e 
      where e.personid = p.personid 
      and e.examresult > 0 ) ) as examcount,
  sum( 
    ( select count(1) from sampletest s 
      where s.personid = p.personid
      and s.result > 0 and s.result < 4) ) as samplecount
from
  person p
group by
  p.state,
  p.gender