显示数据库表中的动态范围并计算每个范围内的行 [英] Display dynamic ranges from a database table and count the rows within each range

问题描述

我有这样的数据库表:

我想显示不同的5岁年龄段以及该年龄段的学生人数，如下所示:

I want to display different 5-year age ranges and the counts of students that are in that range like below:

在这里，最低年龄是10岁，因此我们首先计算范围10-15.该范围内有5个学生.对于第二个范围，我们需要找到年龄> 15(即18).因此，第二个范围是18-23，依此类推.如果能自动计算范围并计算该范围内的数据，我将不胜感激.

Here, the lowest age is 10 so we first calculate the range 10-15. There are 5 students within that range. For the second range, we need to find the age>15 which is 18. So, the second range is from 18-23, and so on. I would appreciate any help where the range is automatically calculated and count the data within that range.

推荐答案

您可以在SUM()语句中使用条件来获取该条件所在的计数.我会计算年龄在BETWEEN()必要范围内的条件.试试这个:

You can use a condition inside of a SUM() statement to get a count where that condition holds. I would count the conditions where the age is BETWEEN() the necessary range. Try this:

SELECT 
  SUM(age BETWEEN 10 AND 15) AS '10-15',
  SUM(age BETWEEN 18 AND 23) AS '18-23',
  SUM(age BETWEEN 26 AND 31) AS '26-31',
  SUM(age BETWEEN 34 AND 39) AS '34-39'
FROM myTable;

这只会返回一行，但是它将拥有您需要的所有内容.这是一个 SQL Fiddle 示例.

This will only return one row, but it will have everything you need. Here is an SQL Fiddle example.

编辑我误解了您的问题，无法自动计算各个范围.我将在此保留先前的答案，因为这可能对将来寻求硬编码范围的读者有所帮助.为此，您必须设置一个变量.我做了一种运行总类型方法来获取组.我首先在查询之前将@a设置为0.然后，我需要获取两个值:

EDIT I misunderstood your question to automatically calculate the various ranges. I will leave my previous answer here because it may be beneficial to future readers looking for hard coded ranges. To do this, you'll have to set up a variable. I made a sort of running total type approach to get the groups. I started by setting @a to 0 before the query. Then, I needed to get two values:

• 该表中的最低年龄，其中age> @a
• 比该变量大5.

我通过根据需要更改@a的值来做到这一点:

I did this by changing the value of @a as necessary:

• @a:=(从myTable年龄> = @a中选择最小(年龄)
• @a:= @a + 5

然后，我将它们包含在CONCAT()块中，并将这些值转换为char以获得所需的组.它可能看起来很复杂，所以我希望我能解释一下这个概念:

Then, I included these in a CONCAT() block and casted these values as chars in order to get the groups that I needed. It may look complicated, so I hope I explained the concept:

SELECT CONCAT
   (CAST(@a := (SELECT MIN(age) FROM myTable WHERE age > @a) AS CHAR), 
   ' - ', 
   CAST((@a := @a + 5) AS CHAR)) AS ageRange
FROM myTable
WHERE @a <= (SELECT MAX(age) FROM myTable);

这样做使我得到了四行，每行都有您期望的年龄范围.我必须添加where子句，因为否则我将为表中的每一行得到一个结果行，这将为我们提供几个空行.

Doing this gave me four rows, each with the age ranges you expect. I had to add the where clause because otherwise I would get one result row for each row in the table, which would give us several null rows.

最后，我添加了一个子查询来获取年龄在必要范围内的学生人数.请注意，第一部分更改了@a的值，因此，不是从@a检查为@a + 5，而是从@ a-5检查为@a.这是最终查询:

Last, I included a subquery to get the count of students whose age is within the necessary range. Note that the first part changes the values of @a, so instead of checking from @a to @a + 5, I check from @a-5 to @a. Here is the final query:

SET @a = 0;
SELECT CONCAT(CAST(@a := (SELECT MIN(age) FROM myTable WHERE age > @a) AS CHAR), ' - ', CAST((@a := @a + 5) AS CHAR)) AS ageRange,
  (SELECT COUNT(*) FROM myTable WHERE age BETWEEN @a - 5 AND @a) AS numStudents
FROM myTable
WHERE @a <= (SELECT MAX(age) FROM myTable)
GROUP BY ageRange;

在 SQL小提琴中，它的运行非常漂亮.完全动态，并返回5个不同的组，而无需事先知道要取哪个组.

It worked beautifully in SQL Fiddle. Completely dynamic and returns the various groups of 5 without any prior knowledge of which groups to take.

这篇关于显示数据库表中的动态范围并计算每个范围内的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！