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屋!