计算特定范围的记录数sql server

本文介绍了计算特定范围的记录数sql server的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图写一个查询来计算基于多个不同范围的记录数。

我使用

这是我做的：

选择count（col1）作为range1 来自tbl1 其中col1 15001和col1 <= 30000 union 选择计数（col1）作为范围3 来自tbl1 其中col1> 30001和col1 <= 45000 etc ...

我使用sql server 2008像我上面所说的，我是积极的有一个更好的方法来做这个，也许这样： sql计数，

编辑：是的，数据库是sql 2008，工作完全根据需要。我忘了提到我实际上正在读一个通过coldfusion 序列化的 JSON 文件 > serializeJSON 。所以在数据库中，下面的一切都是完美的，但是ColdFusion查询查询不支持 CASE 语句，或者它不出现。

解决方案

一种方法是使用条件求和（针对单独列中的值）：

选择sum（当col1 15001且col1 30001 and col1 <= 45000 then 1 else 0 end）as range3 from tbl1; 在单独的行上的值）：

select（case col1 <= 15000 then'range1' > 15001和col1 30001和col1 15001和col1< = 30000 then'range2'当col1> 30001和col1< = 45000然后'range3' else'other' end）;

我经常使用此表单的子查询：

select（*） from（select t。*，（col1 15001且col1 30001且col1 <= 45000时，则'range3' else'other' end） from tbl1 按范围分组;

code> range 只出现一次。

EDIT：

上面的逻辑错过了 15001 和 30001 的值，我的猜测是OP实际上意味着 col1> 15000和col1 <= 30000 和 col1> 30000和col1 <= 45000 for the conditions。但是，我不改变它们，因为上面是原始问题的语句（也许有一些特殊的 15001 和 30001 ）。

I am trying to write a query to count the number of records based on a number of different ranges.

I have success with using union, but I feel there is a better way to do it.

Here is what I've done:

select count(col1) as range1 from tbl1 where col1 <= 15000 union select count(col1) as range2 from tbl1 where col1 > 15001 and col1 <= 30000 union select count(col1) as range3 from tbl1 where col1 > 30001 and col1 <= 45000 etc...

I am using sql server 2008. Like I stated above, I'm positive there is a better way to do this, maybe something like this: sql count,

EDIT: Yes, the database is sql 2008, and the answers below work exactly as needed. I forgot to mention that I'm actually reading a JSON file that has been serialized via coldfusion serializeJSON. So in the db, everything below worked perfectly, but coldfusion query of queries doesn't support the CASE statement, or it doesn't appear to.

解决方案

One way is with conditional summation (for the values in separate columns):

select sum(case when col1 <= 15000 then 1 else 0 end) as range1, sum(case when col1 > 15001 and col1 <= 30000 then 1 else 0 end) as range2, sum(case when col1 > 30001 and col1 <= 45000 then 1 else 0 end) as range3 from tbl1;

Another way is with group by (for the values on separate rows):

select (case when col1 <= 15000 then 'range1' when col1 > 15001 and col1 <= 30000 then 'range2' when col1 > 30001 and col1 <= 45000 then 'range3' else 'other' end) as range, count(*) as cnt from tbl1 group by (case when col1 <= 15000 then 'range1' when col1 > 15001 and col1 <= 30000 then 'range2' when col1 > 30001 and col1 <= 45000 then 'range3' else 'other' end);

I often use a subquery for this form:

select range, count(*) from (select t.*, (case when col1 <= 15000 then 'range1' when col1 > 15001 and col1 <= 30000 then 'range2' when col1 > 30001 and col1 <= 45000 then 'range3' else 'other' end) as range from tbl1 group by range;

That way, the definition of range only appears once.

EDIT:

The above all use the logic from the OP. However, the above logic misses the values of 15001 and 30001. My guess is that the OP really means col1 > 15000 and col1 <= 30000 and col1 > 30000 and col1 <= 45000 for the conditions. But, I'm not changing them because the above is how the original question is phrased (perhaps there is something special about 15001 and 30001).