SQL 中 m/n 连接表上的计数值 [英] Count values over m/n connected tables in SQL

问题描述

我有四张表，想知道某个名称有多少个位置和下载次数.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屋！