用两张表统计下载量 [英] Count downloads with two tables

问题描述

我有两张表，想知道某个名称的下载次数.这是我的桌子

I have two table and like to know how many downloads a certain name has. Here are my tables

表名称"

ID | name
==========
 1 | foo
 2 | bar
 3 | zoo
 4 | luu

表下载"

ID | name_id | timestamp
=========================
 1 | 1       | 1394041682
 2 | 4       | 1394041356
 3 | 1       | 1394041573
 4 | 3       | 1394041981
 5 | 1       | 1394041683

结果应该是:

ID | name | downloads
=====================
 1 | foo  | 3
 2 | bar  | 0
 3 | zoo  | 1
 4 | luu  | 1

这应该很容易...

推荐答案

这很容易.只需 JOIN 表和 COUNT 行.不要忘记 GROUP BY 和 COUNT(timestamp) 所以如果没有，你可以得到零

It is pretty easy. Just JOIN tables and COUNT rows. Don't forget to GROUP BY, and COUNT(timestamp) so you could get zero if there isn't any

  SELECT n.ID,
         n.name,
         COUNT(timestamp) as downloads
    FROM names n 
         LEFT JOIN downloads d 
              ON d.name_id = n.id
GROUP BY n.ID,
         n.name
ORDER BY n.ID;

SQL 小提琴

这篇关于用两张表统计下载量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！