在PostgreSQL中，如何在操作另一个函数(如count)的同时联接两个表 [英] In PostgreSQL how do you join two tables whilst operating another function such as count

问题描述

接着从我之前有关的问题连接我现在在连接和使用计数功能进行比较时遇到了麻烦.

Following on from my prior question about joins I'm now having trouble with joins and comparing using count function.

我有一张表，称为主题

subno   subname   quota
30006   Math      300    
31445   Science   400 
31567   Business  250

我还有另一个表叫做入学人数"

I also have a another table called enrollment

subno sno   
30009 980008
4134  988880
31567 900890

等(在此处转换为SQLFiddle: http://sqlfiddle.com/#!12/dcd01 - -克雷格(Craig)

etc. (Converted to SQLFiddle here: http://sqlfiddle.com/#!12/dcd01 -- Craig)

我如何列出科目编号和名称，其配额少于科目的平均配额.这意味着我需要计算一张桌子上的学生人数，然后与另一张桌子比较正确吗?

How do i List subject number and name which quota is less than the average quota of subjects. This means i need to count the number of students in one table and compare with the other table correct?

推荐答案

最终确定问题(从评论中推导出)为:

After finally determining the question (deduced from comments) to be:

列出所有有空缺的科目

List all subjects with vacancies

您需要的查询是:

select
    subno,
    subname,
    quota,
    quota - count(sno) as vacancies
from subjects s
left join enrollments e on e.subno = s.subno
group by 1, 2, 3
having quota - count(sno) > 0

我还在列vacancies中添加了一个列，该列显示了剩余的空缺数量.

I also added in a column vacancies, which displays the number of vacancies remaining.

注意:您拼写了"enrolls"(拼写错误)(正确的拼写只有一个 L)-我建议您将表格重命名为正确的拼写，以免将来造成混淆.

Note: You have misspelled "enrolments" (correct spelling has only one L) - I recommend you rename your table to the correct spelling to avoid future confusion.

这篇关于在PostgreSQL中，如何在操作另一个函数(如count)的同时联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！