代码之家 › 专栏 › 技术社区 › madlan

使用COUNT的多个Select语句

aggregate-functions oracle sql

madlan · 技术社区 · 14 年前

我试图将这两个select查询组合起来,但是count函数使我与Oracle混淆了:

select person.first_name, COUNT(person.PERSON_ID) as Active
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
and incident.incident_id = 1
AND (incident.dept_id = 111 OR incident.dept_id = 222)
GROUP BY person.first_name;


select person.first_name, COUNT(person.PERSON_ID) as NonActive
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
AND incident.incident_id = 2
AND (incident.dept_id = 111OR incident.dept_id = 222)
GROUP BY person.first_name

FIRST_NAME ACTIVE NonActive
Bob          5       11
John         3       14

做这件事的最佳(有效)方法是什么?

2 回复 | 直到 14 年前

Conrad Frix 14 年前

有两种方法。我很肯定SUM CASE会更好但你可以自己测试

select person.first_name, 
SUM(case when incident.incident_id =1 THEN 1 ELSE 0 END) as Active,
SUM(case when incident.incident_id =2 THEN 1 ELSE 0 END) AS NonActive,

FROM incident, person
where person.PERSON_ID = incident.OWNER_ID

   AND (incident.dept_id = 111 OR incident.dept_id = 222)
   AND incident.incident_id IN (1,2)

GROUP BY person.first_name;

使用连接

SELECT  DISTINCT
    p.First_name,
    Acive.Active,
    NonActive.NonActive
FROM
PERSON p

LEFT JOIN 
(
select person.first_name, COUNT(person.PERSON_ID) as Active
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
and incident.incident_id = 1
AND (incident.dept_id = 111 OR incident.dept_id = 222)
GROUP BY person.first_name;
) Active
ON p.first_name = active.first_name
LEFT JOIN 
(
select person.first_name, COUNT(person.PERSON_ID) as NonActive
FROM incident, person
where person.PERSON_ID = incident.OWNER_ID
AND incident.incident_id = 2
AND (incident.dept_id = 111OR incident.dept_id = 222)
GROUP BY person.first_name
) NonActive

ON p.first_name = NonActive.first_name

where Active.First_name is not null
and NonActive.First_name is not null

Paul Sonier 14 年前

我相信你想依靠事件id而不是个人id;这样,你可以将两个查询合并为一个,得到你想要的结果。

推荐文章

Franz Biberkopf · Oracle:组合子查询和聚合函数

2 年前

Himanshu Pant · 在Teradata中实现聚合

6 年前

Niels Kristian · 如何根据不同行的左外连接关系中的属性生成函数值

6 年前

Vinay Ranjan Shukla · 使用max(column1)打印行中的column2,但不在group by子句中包含column2

7 年前

user4398985 · 通过维护订单聚合重复记录,还包括重复记录

7 年前

Igneous01 · SQL-添加到现有平均值

7 年前

ALM · 使用Spring JPA通过Postgresql为Group\u设置STRING\u AGG的聚合函数

7 年前

Konstantinos Vidakis · 列在选择列表中无效,因为它不包含在聚合函数或GROUP BY子句中。[ntext][duplicate]

7 年前

CairoCoder · MySQL 5.7 | group by | distinct |聚合错误

7 年前

Kvvaradha · MySQL查询简化

7 年前