SQL 中 m/n 连接表上的计数值 [英] Count values over m/n connected tables in SQL
本文介绍了SQL 中 m/n 连接表上的计数值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有四张表,想知道某个名称有多少个位置和下载次数.names
和 locations
通过 names_locations
表
I have four tables and like to know how many locations and downloads a certain name has.
names
and locations
are connected via the names_locations
table
这是我的表格:
表names
"
ID | name
=========
1 | foo
2 | bar
3 | zoo
4 | luu
表locations
"
ID | location
=============
1 | Hamburg
2 | New York
3 | Singapore
4 | Tokio
表names_locations
"
ID | location_id | name_id
==========================
1 | 1 | 1
2 | 1 | 2
3 | 2 | 2
4 | 3 | 3
5 | 1 | 2
表下载
"
ID | name_id | timestamp
=========================
1 | 1 | 1394041682
2 | 4 | 1394041356
3 | 1 | 1394041573
4 | 3 | 1394041981
5 | 1 | 1394041683
结果应该是:
ID | name | locations | downloads
=================================
1 | foo | 1 | 3
2 | bar | 3 | 0
3 | zoo | 1 | 1
4 | luu | 0 | 1
这是我的尝试(没有下载列):
Here's my attempt (without the downloads column):
SELECT names.*,
Count(names_locations.location_id) AS location
FROM names
LEFT JOIN names_locations
ON names.ID = names_locations.name_id
GROUP BY names.ID
推荐答案
我认为这可行.
SELECT n.id,
n.name,
COUNT(DISTINCT l.id) AS locations,
COUNT(DISTINCT d.id) AS downloads
FROM names n LEFT JOIN names_location nl
ON n.id = nl.name_id
LEFT JOIN downloads dl
ON n.id = dl.name_id
LEFT JOIN locations l
ON l.id = nl.location_id
GROUP BY n.id, n.name
这篇关于SQL 中 m/n 连接表上的计数值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文