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

问题描述

三天来，我一直在想办法解决这个问题.一般来说，我是 Maria db 和 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.

我有如下三张表:

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 这意味着我首先必须从他们各自的表中派生出学生和教师，然后带入 zipcode 表来比较他们的邮政编码和城市.

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屋！