Question

我需要写一个问题,即从表格中回收合计数据和非合计数据。

以下例子应有助于澄清我想要做些什么。我有以下(简化)表格:

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

为了论证起见,请假设每个学生都属于一只课程组。我想写一个回类似结果的提问:

学生名称、学生体重、体重

www.un.org/Depts/DGACM/index_spanish.htm 地点:

<>strong> 体重-apgaw: 是每个学生的重量,按他/她所属课程组的平均重量百分比表示。

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

我对如何撰写这种询问有点看法(也有一些想法)。我的办法是撰写两个问题,有些是两个表格之间的研究,但这种做法似乎不明智,而且效果最好不彰。

是否有人可以建议我如何正确写出这种结构的功能? 理想的情况是,这应该是荒谬的。然而,我正在使用PogreSQL 8.4,因此,如果我不得不在Kingk之间作出选择,则这具有优先地位。

Answer 1

a 开始按这些思路提问:

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

Answer 2

http://www.postgresql.org/docs/9.1/static/tutorial-window.html - 同样:

select name,
       weight,
       height,
       100 * weight / avg(weight) over (partition by grp_id) weight_apgaw,
       100 * weight / avg(height) over (partition by grp_id) weight_apgh
from student

友情链接