SQL查询返回汇总和非汇总结果 [英] SQL query that returns aggregate AND non aggregate results

问题描述

我需要编写一个查询，该查询返回表中的聚合数据和非聚合数据。

下面的示例应有助于阐明我要尝试的内容做。我在下面有以下（简化的）表：

 创建表course_group_def（
 id主序列键，
名称TEXT NOT NULL 
）; 
 
创建表学生（
 id主序列号，
 grp_id整数引用参考资料course_group_def（id）更新级联，
名称TEXT NOT NULL，
重float NOT NULL，
的高度float NOT NULL 
）;为了争辩，让我们假设每个学生都属于一个课程组，并且是唯一的课程组。我想编写一个查询，该查询将返回如下结果集：
 
 
 
 student.name，student.weight，weight_apgaw，weight_apgh 
 
 
 
 其中： 
 
 
 
  weight_apgaw：是单个学生的体重，表示为他/她所属课程组平均体重的百分比。
 
 
 
  weight_apgh： 
是单个学生的体重，表示为他/她所属课程组平均高度的百分比。
 
 
 
我不知道如何写这样的文章。查询。我的方法是编写两个查询，并以某种方式在两个表之间进行查找-但这种方法充其量似乎无济于事且效率低下。
 
 
 
有人可以建议如何我可以正确编写这样的SQL函数吗？理想情况下，这应该与数据库无关。但是，我使用的是PostgreSQL 8.4，因此如果必须在SQL风格之间进行选择，则优先使用。
解决方案
这些行应该可以帮助您入门：
 选择s.name 
，s.weight 
，（（ s.weight / st.avgweight）* 100）作为weight_apgaw 
，（（s.height / st.avgheight）* 100）as weight_aphei 
来自学生s 
加入（
从grp_id 
的学生
组中选择grp_id 
，avg（weight）作为avgweight 
，avg（height）作为avgheight 
）st在s.grp_id = st。 grp_id 
  
 
I need to write a query that returns both aggregate and non-aggregate data from a table.

The example below, should help clarify what I'm trying to do. I have the following (simplified) tables below:
CREATE TABLE course_group_def (
          id        PRIMARY SERIAL KEY,
          name      TEXT NOT NULL
          );

CREATE TABLE student (
          id        PRIMARY SERIAL KEY,
          grp_id    INTEGER REFERENCES course_group_def(id) ON UPDATE CASCADE,
          name      TEXT NOT NULL,
          weight    float NOT NULL,
          height    float NOT NULL
          );
For the sake of argument, lets assume that each student belongs to one and only course group. I want to write a query that will return a result set like this:

student.name, student.weight, weight_apgaw, weight_apgh

Where:

weight_apgaw: is the weight of the individual student, expressed as a percentage of the average weight of the course group he/she belongs to.

weight_apgh: 
is the weight of the individual student, expressed as a percentage of the average height of the course group he/she belongs to

I have NO idea (well some idea) about how to go about writing such a query. My approach would be to write two queries, and somehow do a lookup between the two tables - but such an approach seems nonsensical and inefficient at best.

Could someone please suggest how I may correctly write such an SQL function?. Ideally, this should be db agnostic. However, I am using PostgreSQL 8.4, so if I have to choose between SQL flavours, this takes precedence.
解决方案
a query along these lines should get you started:
select s.name
     , s.weight
     , ((s.weight/st.avgweight) * 100) as weight_apgaw
     , ((s.height/st.avgheight) * 100) as weight_aphei
from student s 
join (
   select grp_id
        , avg(weight) as avgweight
        , avg(height) as avgheight
   from student
   group by grp_id
   ) st on s.grp_id = st.grp_id


                        
这篇关于SQL查询返回汇总和非汇总结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！