我正尝试在DESC顺序中根据记录在下载表中出现的次数输出一系列连接记录。使用PHP对众多MySQL表进行分组和排序
资源数据(resource_data):
----------------------------------------
| ID | Contributor ID | Title |
----------------------------------------
| 1 | 111 | Resource A |
| 2 | 222 | Resource B |
| 3 | 333 | Resource C |
| 4 | 222 | Resource D |
| 5 | 111 | Resource E |
----------------------------------------
Contributor数据(contributor_data):
----------------------
| ID | User Name |
----------------------
| 111 | User X |
| 222 | User Y |
| 333 | User Z |
----------------------
下载数据(download_data):
---------------------
| ID | Resource ID |
---------------------
| 1 | 4 |
| 2 | 1 |
| 3 | 4 |
| 4 | 3 |
| 5 | 4 |
| 6 | 4 |
| 7 | 2 |
| 8 | 5 |
| 9 | 4 |
| 10 | 5 |
---------------------
当排序,数据应该出来的东西等(未格式化,只是内容):
4 222 Resource D User Y (5 records)
5 111 Resource E User X (3 records)
2 222 Resource B User Y (2 records)
3 333 Resource C User Z (1 record)
1 111 Resource A User X (1 record)
到目前为止,我有这样的代码,但它只是把出一条记录(应该有很多,有限根据查询为4)。
SELECT * FROM resource_data, contributor_data, download_data
WHERE resource_data.contributor_id=contributor_data.id
AND download_data.resource_id=resource_data.id
ORDER BY count(download_data.resource_id) DESC LIMIT 4