SELECT COUNT（*） - 如果没有匹配的行，返回0和分组字段 [英] SELECT COUNT(*) - return 0 along with grouped fields if there are no matching rows

问题描述

我有以下查询：

  SELECT employee，department，count（*）AS sum FROM items 
 WHERE（（employee = 1 AND department = 2）OR 
（employee = 3 AND department = 4）OR 
（employee = 5 AND department = 6）OR 
相同结构]））
 AND available = true 
 GROUP BY员工，部门; 
  

如果对employee-department没有项，则查询不返回任何内容。我希望它返回零：

  employee |部门| sum 
 --------- + ------------ + -------- 
 1 | 2 | 0 
 3 | 4 | 12 
 5 | 6 | 1234 
  

EDIT1

不可能，因为Matthew PK在回答类似问题时解释了 。我错误地假定Postgres可能从WHERE子句中提取缺失的值。

EDIT2

技能。 :)感谢Erwin Brandstetter！

解决方案

不可能？接受挑战。 ：）

  WITH x（员工，部门）AS（
 VALUES 
（1 :: int， 2 :: int）
，（3，4）
，（5，6）
  -  ...更多组合
）
 SELECT x.employee， x.department，count（i.employee）AS ct 
 FROM x 
 LEFT JOIN items i ON i.employee = x.employee 
 AND i.department = x.department 
和i.available 
 GROUP BY x.employee，x.department; 
  

这会给你如果 employee 和部门不是整数，则转换为匹配类型。

来自@ypercube的每个注释：count（）需要在项的非空列上，所以我们得到 0 c>

此外，还可以添加其他条件进入 LEFT JOIN 条件（在本例中为 i.available ），因此不排除不存在的条件

性能

在评论中解决其他问题。

。使用较长的条件列表，（LEFT）JOIN 可能是最快的方法。

请尽可能创建多列索引，如： / p>

  CREATE INDEX items_some_name_idx ON items（employee，department）; 
  

如果（employee，department） PRIMARY KEY 或者你应该在两列上有一个 UNIQUE 约束，这也会做的。 / p>

I have the following query:

SELECT employee,department,count(*) AS sum FROM items 
WHERE ((employee = 1 AND department = 2) OR 
      (employee = 3 AND department = 4) OR 
      (employee = 5 AND department = 6) OR 
      ([more conditions with the same structure]))
      AND available = true
GROUP BY employee, department;

If there are no items for a pair "employee-department", then the query returns nothing. I'd like it to return zero instead:

 employee | department | sum 
 ---------+------------+--------
 1        |          2 |      0
 3        |          4 |     12  
 5        |          6 |   1234   

EDIT1

Looks like this is not possible, as Matthew PK explains in his answer to a similar question. I was mistakenly assuming Postgres could extract missing values from WHERE clause somehow.

EDIT2

It is possible with some skills. :) Thanks to Erwin Brandstetter!

解决方案

Not possible? Challenge accepted. :)

WITH x(employee, department) AS (
   VALUES
    (1::int, 2::int)
   ,(3, 4)
   ,(5, 6)
    -- ... more combinations
   )
SELECT x.employee, x.department, count(i.employee) AS ct
FROM   x
LEFT   JOIN items i ON i.employee = x.employee
                   AND i.department = x.department
                   AND i.available
GROUP  BY x.employee, x.department;

This will give you exactly what you are asking for. If employee and department aren't integer, cast to the matching type.

Per comment from @ypercube: count() needs to be on a non-null column of items, so we get 0 for non-existing critera, not 1.

Also, pull up additional criteria into the LEFT JOIN condition (i.available in this case), so you don't exclude non-existing criteria.

Performance

Addressing additional question in comment.
This should perform very well. With longer lists of criteria, (LEFT) JOIN is probably the fastest method.

If you need it as fast as possible, be sure to create a multicolumn index like:

CREATE INDEX items_some_name_idx ON items (employee, department);

If (employee, department) should be the PRIMARY KEY or you should have a UNIQUE constraint on the two columns, that would do the trick, too.

这篇关于SELECT COUNT（*） - 如果没有匹配的行，返回0和分组字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！