为什么count（*）在此子查询（postgresql）中返回多个结果？

编程入门行业动态更新时间:2024-10-27 11:18:22

本文介绍了为什么count（*）在此子查询（postgresql）中返回多个结果？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我想将count（*）查询的结果分组到值存储桶中。我正在dellstore2 postgresql示例数据库中对此进行测试。我在下面的查询返回正确的答案，但是对表中的每一行都执行一次（几千个相同的结果）。我可以通过在查询末尾添加 LIMIT 1 来解决此问题，但我想理解为什么要得到重复项以防它指向更广泛的问题我的方法。查询是：

SELECT （SELECT count（*） FROM order 其中总金额> 0并且总金额 100并且总金额<= 200）AS> 100 <== 200 ... 来自订单；

EDIT Andomar的回答也使我找到了以下方法（改编自简而言之就是SQL（O'Reilly））。这使我可以将存储桶放在一列中，每个存储桶/答案配对都有一行。我以为应该将其包含在该用例中：

选择情况当总金额为NULL时'未知'当总金额<= 100则'不超过100'当总额<= 200则'不超过200' ELSE'超过200' END Bucket， COUNT（*）结果数来自订单案例分析当总量为NULL则'未知'当总金额<= 100然后'不超过100'当总金额<= 200 THEN'不超过200'其他'超过200'结束预定至少 MIN（总金额）；

解决方案

您要从订单，然后为每一行评估子查询。

请考虑以下方法：

选择计数（当0 这将在一次表扫描中计算两个聚合。

I want to group the results of a count(*) query into value buckets. I'm testing this on the dellstore2 postgresql sample database. My query below returns the right answers, but does so once for each row in the table (several thousand identical results). I can fix this by adding LIMIT 1 the the end of the query but I'd like to understand why I'm getting the duplicates in case it points to a wider problem with my approach. The query is:

SELECT (SELECT count(*) FROM orders WHERE totalamount > 0 AND totalamount <= 100) AS ">0 <= 100", (SELECT count(*) FROM orders WHERE totalamount > 100 AND totalamount <= 200) AS ">100 <= 200" ... FROM orders;

EDIT Andomar's answer also allowed me to find the following approach (adapted from an example in SQL in a nutshell (O'Reilly)). This lets me have the buckets in one column, with a row for each bucket/answer pairing. I thought I'd include it for anyone with that use-case:

SELECT CASE WHEN totalamount IS NULL THEN 'Unknown' WHEN totalamount <= 100 THEN 'Not more than 100' WHEN totalamount <= 200 THEN 'Not more than 200' ELSE 'Over 200' END "Bucket", COUNT(*) "Number of results" FROM orders GROUP BY CASE WHEN totalamount IS NULL THEN 'Unknown' WHEN totalamount <= 100 THEN 'Not more than 100' WHEN totalamount <= 200 THEN 'Not more than 200' ELSE 'Over 200' END ORDER BY MIN(totalamount);

解决方案

You're selecting every row from orders, and then for each row, the subqueries are evaluated.

Consider this approach instead:

select count(case when 0 < totalamount and totalamount <= 100 then 1 end) as "<0,100]" , count(case when 100 < totalamount and totalamount <= 200 then 1 end) as "<100,200]" from Orders