Maria DB如何在选择查询中使用组，联合和/或总和/计数 [英] Maria db how to use groups, unions, and/or sum/count in a select query

问题描述

我已经三天不停地尝试着解决这个问题.我是Maria数据库和sql的新手.在以前的类似情况下，我设法使用了UNION，但在这种情况下不起作用.

I've been breaking my mind for three days trying to puzzle this one out. I'm new to Maria db and sql in general. I've managed to use UNION on a previous similar situation but it's not working in this one.

我有三个表，如下所示:

I have three tables as follows:

create table zipcode (zip int, city varchar(30))
create table student (id int, zip_fk int)
create table teacher (id int, zip_fk int)

我想创建一个选择查询，该查询将具有以下字段:城市，城市学生的人数，城市老师的人数以及城市学生和老师的总数.本质上，结果应按城市分组.我完全不知所措.

I want to create a select query that will have the following fields: city, the number of students from the city, the number of teachers from the city, and the total number of students and teachers from the city. Essentially, the results should be grouped by city. I am at a complete loss.

编辑.我面临的挑战是city字段位于另一个表中，而不是主键或外键.因此，我不能直接使用它.主键是zip，这意味着我首先必须从他们各自的表中派生学生和教师，然后输入邮政编码表以将其邮政编码与城市进行比较.

Edit. The challenge I am facing is that the city field is located in a different table and is not a primary key or a foreign key. As such, I cannot directly use it. The primary key is zip which means I first have to derive students and teachers from their respective tables, then bring in the zipcode table to compare their zip with cities.

推荐答案

这很棘手.这是使用union all和group by的一种方法:

This is rather tricky. Here is one method using union all and group by:

select city, sum(student) as students, sum(teacher) as teachers
from ((select z.city, 1 as student, 0 as teacher
       from student s join
            zipcode z
            on s.zip_fk = z.zip
      ) union all
      (select z.city, 0 as student, 1 as teacher
       from teacher t join
            zipcode z
            on t.zip_fk = z.zip
      )
     ) st
group by city;

这篇关于Maria DB如何在选择查询中使用组，联合和/或总和/计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！