如何计算一个表中的行基于mysql中的另一个表 [英] How to count rows in one table based on another table in mysql

问题描述

我在MySQL数据库中有两个表。第一个有部门名称列表。

I have two tables in a MySQL database. The first one has a list of department names.

departments
    abbreviation | name
    -------------|-------------
    ACC          | accounting
    BUS          | business
    ...

第二个表具有名称包含部门的缩写。

The second table has a list of courses with names that contain the department's abbreviation.

courses
    section      | name
    -------------|-------------
    ACC-101-01   | Intro to Accounting
    ACC-110-01   | More accounting
    BUS-200-02   | Business etc.
    ...

我想写一个查询，对于 departments 表中的每一行，请告诉我课程表中有多少行像缩写我有。这样的东西：

I'd like to write a query that will, for each row in the departments table, give me a count of how many rows in the courses table are like the abbreviation I have. Something such as this:

    abbreviation | num
    -------------|--------------
    ACC          | 2
    BUS          | 1
    ...

我可以为一个单独的部门使用查询

I can do this for one individual department with the query

SELECT COUNT(*) FROM courses WHERE section LIKE '%ACC%'
    (gives me 2)

虽然我可以循环遍历PHP并执行上面的查询多次，我宁愿在单个查询。这是我想要的伪代码...

Although I could loop through in PHP and do the above query many times, I'd rather do it in a single query. This is the pseudocode I'm thinking of...

SELECT department.abbreviation, num FROM
    for each row in departments
        SELECT COUNT(*) AS num FROM classes WHERE section LIKE CONCAT('%',departments.abbreviation,'%)

任何想法？

推荐答案

SELECT d.abbreviation, COUNT(*) num
FROM departments d
INNER JOIN courses c ON c.section LIKE CONCAT(d.abbreviation, "%")
GROUP BY d.abbreviation

Sql Fiddle

Sql Fiddle

这篇关于如何计算一个表中的行基于mysql中的另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！