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

2022-01-09 00:00:00 sql sql-server coldfusion

我正在尝试编写一个查询来计算基于多个不同范围的记录数.

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

我在使用 union 方面取得了成功，但我觉得有更好的方法.

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

这是我所做的:

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...

我正在使用 sql server 2008.就像我上面所说的，我很肯定有更好的方法来做到这一点，也许是这样的:sql 计数范围内计数发生次数，

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,

是的，数据库是 sql 2008，下面的答案完全可以根据需要工作.我忘了提到我实际上正在读取一个 JSON 文件，该文件已通过 Coldfusion serializeJSON 进行了 serialized.所以在数据库中，下面的一切都运行良好，但是查询的冷融合查询不支持 CASE 语句，或者似乎不支持.

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;

另一种方法是使用 group by(用于单独行上的值):

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;

这样，range的定义只出现一次.

That way, the definition of range only appears once.

以上都使用了OP的逻辑.但是，上面的逻辑漏掉了15001和30001的值.我的猜测是 OP 真的意味着 col1 >15000 和 col1 <= 30000 和 col1 >30000 和 col1 <= 45000 的条件.但是，我不会更改它们，因为上面是原始问题的措辞方式(也许 15001 和 30001 有一些特别之处).

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).

相关文章