24
4
I have two tables: class and student. I want to get all existing classes and get number (counted) of students per given class. Trick is I want to show also a class that has 0 student.
我有两张桌子：班级和学生。我想得到所有现有的班级，并得到学生的数量(计数)每个给定的班级。诀窍是我想展示一个有0个学生的班级。
Table "class":
表“类”：
个人中心
文章发布
退出DROP TABLE IF EXISTS "class";
CREATE TABLE class(
class_id SERIAL PRIMARY KEY,
class_name VARCHAR
);
INSERT INTO class (class_name) VALUES ('A');
INSERT INTO class (class_name) VALUES ('B');
INSERT INTO class (class_name) VALUES ('C');
Table "student":
表“学生”：
DROP TABLE IF EXISTS "student";
CREATE TABLE student(
student_id SERIAL PRIMARY KEY,
student_name VARCHAR,
class_id numeric
);
INSERT INTO student (student_name, class_id) VALUES ('Mike', 1);
INSERT INTO student (student_name, class_id) VALUES ('Jessica', 1);
INSERT INTO student (student_name, class_id) VALUES ('Thomas', 1);
INSERT INTO student (student_name, class_id) VALUES ('Jacob', 2);
INSERT INTO student (student_name, class_id) VALUES ('Izabella', 2);
My query works, but does NOT show the class C with 0 students in:
我的查询起作用了，但没有显示C类中有0名学生：
SELECT class_name, student.class_id, COUNT(DISTINCT student_id) AS num_students
FROM student
LEFT JOIN class ON student.class_id = class.class_id
GROUP BY student.class_id, class.class_name;
Above query gives me the following result:
上面的查询给出了以下结果：
| class_name | class_id | num_students |
|---|---|---|
| A | 1 | 3 |
| B | 2 | 2 |
I would love to be able to get something like this instead:
我很希望能够得到这样的东西：
| class_name | class_id | num_students |
|---|---|---|
| A | 1 | 3 |
| B | 2 | 2 |
| C | 3 | 0 |
RIGHT JOIN class ON student.class_id = class.class_id
右加入学生的班级。class_id=class.class_id
If you do left join, you're going to retain left table's values. If your left table is student, that's the table which the join will be based on. If you want to enforce all class values, you need to have class as the left table inside the join (as per answer below), or leave it in the right side and use the right join (as per EAW suggestion).
如果执行左连接，则将保留左表的值。如果您的左侧表是学生表，则连接将基于该表。如果您想强制实施所有类值，则需要将CLASS作为联接中的左表(根据下面的答案)，或者将其留在右边并使用右边的联接(根据EAW建议)。
Btw, doublecheck your ddl that generates the tables: you may not want to use double quotes on table names when you create them, and you also need to specify the amount of varchar you want to allocate for "class.class_name" and "student.student_name" columns.
顺便说一句，请仔细检查生成表的DDL：您可能不希望在创建表名时在表名称上使用双引号，并且还需要指定要分配给“class.class_name”和“Student.Student_name”列的varchar大小。
From the description, it appears that your query is intended to answer the question "For each class, how many unique students are enrolled?" Unfortunately, the query you've written answers a slightly different question; i.e., "For each class with at least one enrolled student, how many unique students are enrolled?"
从描述中可以看出，您的查询似乎是为了回答问题“对于每个班级，注册了多少不同的学生？”不幸的是，您编写的查询回答了一个略有不同的问题；即，“对于每个至少有一个注册学生的班级，有多少独立的学生注册？”
For readability/clarity, I suggest putting class first in the FROM clause since it is the driving table. For queries with more than one table, always qualify the column names so that it's easy to identify the source without having to reference each table's DDL. In the select list, only reference columns from outer joined tables when necessary since they will be NULL if the join criteria isn't met.
为了可读性/清晰度，我建议将class放在FROM子句的第一位，因为它是驱动表。对于具有多个表的查询，始终限定列名，以便很容易识别源，而不必引用每个表的索引。在选择列表中，仅在必要时引用外部联接表中的列，因为如果不满足联接条件，这些列将为NULL。
The following query produces the requested results:
以下查询将生成请求的结果：
SELECT class.class_name, class.class_id, COUNT(DISTINCT student.student_id) AS num_students
FROM class
LEFT JOIN student
ON class.class_id = student.class_id
GROUP BY class.class_id
ORDER BY class.class_name;
Note that this query only groups by class.class_id. Since class_id is the primary key, including other columns from class in the list of grouping expressions is redundant since each is fully dependent on class_id.
请注意，该查询仅按class.class_id分组。由于class_id是主键，因此将CLASS中的其他列包括在分组表达式列表中是多余的，因为每个列都完全依赖于class_id。
There should be a foreign key constraint on student(class_id) referencing class(class_id).
对于引用类(Class_Id)的学生(Class_Id)应该有一个外键约束。
thank you for extensive, thorough reply. It works!
感谢您广泛、彻底的回复。它起作用了!
24
4
0
这里我试图在 FlatList 中显示一个名为“posts”的数组。 render() { console.log(this.props.posts); return (
这是我的代码: {{day(list)}} {{list.weather[0].description}}
我是 Mahout 的新手，并且仍在使用它。 我的问题是，将 Item-Item 和 User-Item 结合起来是否合适？ 我的用例是，一个社交网络应用会尝试根据用户历史数据(优先级较高)为当前用户
下午好， 我按数据库搜索以测试特定类测试，当我放置一个新项目时，如果列表包含该项目。 @Test public void insertAndDeleteTask() throws Interrupte
我有一个关于 ionic 框架的问题，我希望有人能帮助我...我有一个带有“ion-item-right”的 ionic 列表。这一切都可以，按钮在右边。现在我需要其他三个居中的图标，这样我就有了:文
我经常遇到类似下面的代码: if ( items != null) { foreach(T item in items) { //... } } 基本上，if 条件确
我最近问了a question about LocalStorage .使用 JSON.parse(localStorage.item) 和 JSON.parse(localStorage['item
我最近问了a question about LocalStorage .使用 JSON.parse(localStorage.item) 和 JSON.parse(localStorage['item
这个问题已经有答案了: Type mismatch: cannot convert from Item to Item (1 个回答) 已关闭 7 年前。 我很困惑。我无法将外部类的实例变量 Node
我目前正在使用 MUI Grid(但我对替代解决方案持开放态度)并且我想要并排放置两个组件:最右边的组件占 400px宽度和左侧组件占据其余部分。 || || || 当页面宽度缩小时: | | ||
我最近问过a question about LocalStorage 。使用 JSON.parse(localStorage.item) 和 JSON.parse(localStorage['item
public class Document extends Model { ... @ManyToMany public Set accessors; ... } 我想选择访问者包含某个用户的所有文档
我正在使用 selenium webdriver 为单页 Web 应用程序开发一个 Java 框架，使用以下模式:PageObject、SlowLoadableComponent(责任链)、PageF
最近在学习C，在网上发现了一个问题。问题是: What is the problem with this function in terms of memory allocation? What is
我有这个代码 ( -1 ? true : false} /> {genre.item.name}
在ASP.Net中使用DataGrid时真的没有快捷方法吗 (e.Item.ItemType==ListItemType.Item || e.Item.ItemType==ListItemType.A
我正在使用工作流程根据数据和一组要求将大量 pdf 从一个位置复制到其他大坝位置。我正在使用以下代码 Assets damAsset = manager.createAsset(path, is, m
我是 PowerShell 的新手。 我正在尝试自动将 dll 组件从源服务器上的文件夹部署到目标服务器上的多个文件夹。这看起来应该很简单:将组件从源服务器上的源(部署)文件夹复制到目标服务器上的文件
我的代码: for column_name, column_data in summary_words.iteritems(): if column_name != "summary" and
我的代码: for column_name, column_data in summary_words.iteritems(): if column_name != "summary" and
我是一名优秀的程序员,十分优秀！