如何在 1 个 sql 查询中从 4 个表中获取数据? [英] How to get data from 4 tables in 1 sql query?

问题描述

我有以下数据库架构:

table courses:
id
tutor_id
title



table course_categories:
    id
    category_id
    course_id

table categories:
    id
    name

table tutors:
    id
    name

table subscribers:
    id
    course_id
    user_id

我需要制作 1 个 sql 才能获得包含所有类别的课程、该课程的导师以及该课程的订阅者数量.这可以在 1 个查询中完成吗?这应该使用存储过程来完成吗?

I need to make 1 sql to get a course with all it's categories, and the tutor for that course and the number of subscribers for that course. Can this be done in 1 query? Should this be done using stored procedures?

推荐答案

通过这个查询，你得到你想要的:

With this query you get what you want:

select co.title as course,
       ca.name as category,
       t.name as tutor,
       count(s.*) as total_subscribers
from courses co
inner join course_categories cc on c.id = cc.course_id
inner join categories ca on cc.category_id = ca.id
inner join tutors t on co.tutor_id = t.tutor_id
left join subscribers s on co.id = s.course_id
where co.title = 'Cat1'
group by co.title, ca.name, t.name

我在订阅者上使用了left join，因为给定的课程可能没有订阅者.我假设所有其他表上都有每个 course、categorie 和 tutor 的数据.如果没有，您也可以使用 left join ，但这样您的数据将为 null.

I used left join on subscribers because there might be no one for a given course. I'm assuming that all the other tables have data on it for every course, categorie and tutor. If not, you can user left join as well but then you'll have data with null.

这篇关于如何在 1 个 sql 查询中从 4 个表中获取数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！