个人中心
gpt4 book ai didi

Showing all groups and count items in given group (including 0 results)(显示给定组中的所有组和计数项(包括0个结果))

转载 作者：bug小助手 更新时间：2023-10-25 22:13:26 24 4
gpt4 key购买 nike



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
文章推荐： Add confirm delete modal to filament v2 repeater items(将确认删除模式添加到灯丝v2中继器项目)
文章推荐： How can i stop CSS Grid cells from expanding due to text content?(如何阻止由于文本内容而导致的CSS网格单元格的扩展？)
文章推荐： How do I configure laravel sanctum to authorize other domains and that can be consumed by NuxtJS(如何配置LARLAVEL DISATOM以授权其他域，并且可以由NuxtJS使用)
文章推荐： Test fails when it mimics proven documentation(当模拟经验证的文档时，测试失败)
bug小助手
个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章
滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com