mysql内部连接2个表和order by count

Question

我的数据库中有以下表格

项目

+----+-------------------------------------------+
| id | name                                      |
+----+-------------------------------------------+
|  1 | YANNONALI COURT                           |
|  2 | UNIVERSITY OF COLORARDO DENVER RESEARCH 2 |
|  3 | G.R.E.A.T PROGRAM DESALTER BUILDING       |
|  4 | MONARCH CLUB                              |
|  5 | LAFAYETTE MERCANTILE                      |
|  6 | CAMELBACK VILLAGE RAQUET AND HEALTH CLUB  |
|  7 | BACK COUNTRY                              |
|  8 | URBAN CRASHPAD                            |
|  9 | PRIVATE RESIDENCE                         |
| 10 | EATON RESIDENCE                           |
+----+-------------------------------------------+

PROJECT_ASSIGNMENTS(其中projects.id=project_assignment.target_id)

+-------+-----------+-------------+
| id    | target_id | property_id |
+-------+-----------+-------------+
| 19178 |         1 |          48 |
| 19192 |         1 |          39 |
| 19391 |         1 |           3 |
| 19412 |         2 |           3 |
| 19591 |         2 |          34 |
| 19610 |         2 |          34 |
| 21013 |         3 |           2 |
| 21032 |         3 |           2 |
| 30876 |         4 |        2433 |
| 38424 |         5 |        2580 |
+-------+-----------+-------------+

属性(其中properties.id= project_assignment.property_id)

+----+------------------+
| id | name             |
+----+------------------+
|  2 | Residential      |
|  3 | Multi Family     |
| 34 | New Construction |
| 39 | Contemporary     |
| 48 | Southwest        |
+----+------------------+

我希望在列表中按no.of projects订购O/P ...

Residential(177) //12 - total no.of projects which is having this property
Multi Family(15)
New Construction(13)
Contemporary(11)

请给我一些MySQL查询

                       Thank You

Answer 1

这应该能起到作用：

select
    c.name,
    count(c.id) as CountOfProperties
from
    projects a,
    project_assignments b,
    properties c
where
    a.ID=b.target_id
    and b.property_id=c.ID
group by
    c.name
order by
    count(c.id) desc;

Answer 2

试试这个：：

select
    prop.name,
    count(prop.id) as CountOfProperties
from
    projects p
    inner join  project_assignments pa on (p.ID=pa.target_id)
    inner join  properties prop on (pa.property_id=prop.ID)
group by
    prop.name
order by
    count(prop.id) desc;