如何在mysql中基于另一个表计算一个表中的行数

Question

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

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

第二个表格列出了课程名称包含该系缩写的课程。

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

我想写一个查询，对于departments表中的每一行，给我一个计数，计算出和我拥有的缩写类似的courses表中的行数。例如：

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

我可以使用查询语句为单个部门执行此操作。

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

虽然我可以在PHP中循环并执行上述查询多次，但我更愿意用一条查询语句完成。这是我考虑的伪代码...

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

任何想法？

- Zach Hall

Answer 1

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

- Steve Robbins

---

太好了！而且代码比我预期的要少得多。时间也比我预期的要短得多。谢谢！ - Zach Hall

Answer 2

试试这个

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

- reshma

---

欢迎来到StackOverflow！看起来你在这里提出了一个跟进问题，而不是给出一个新的答案。如果是这样的话，你应该提出一个全新的问题，而不是把它作为答案发布在这里。 - DaveyDaveDave

Answer 3

一个快速的解决方案，但不是最好的，可能是：

SELECT abbreviation, 
(SELECT COUNT(*) FROM courses C WHERE D.abbreviation = SUBSTRING(C.abbreviation, 0, 3)) AS c 
FROM departments D;

- Pablo Francisco Pérez Hidalgo

Answer 4

试一试这个：

select
    d.abbreviation,
    count(d.abbreviation) count
from
    departments d
inner join
    courses c
on (LOCATE(d.abbreviation,c.section) <> 0)
group by d.abbreviation;

- Edd

---

实际上，我更喜欢Steve的答案。 - Edd