mysql - 从 3 个不同的表中选择一列

Question

我有 3 张 table :

Table 1: columns are: 

+------+-------+----------+----------+
| date |  time |  user_id |  channel |
+------+-------+----------+----------+

Table 2:

+---------+------------+
| user_id |  id _number |
+---------+------------+

Table 3: 

+---------+-------+
| channel |  sort |
+---------+-------+

In table 1 the columns are sorted as following : 
2011-07-29, 12:35:15.650, 22,  DeluxeMusic
In table 2 : 
130.83.10.c42ce82365b9f6d , 22 (same as user_id in table 1)
In table 3: 
DeluxeMusic (same as in table 1), entertainment.

列 user_id在table 2 :130.83.10.c42ce82365b9f6d表示某个用户的user_id。我需要从这个用户 ID 获取值为 130 的所有条目一开始。对于具有相同值 130 的所有条目。

然后我需要寻找130这个值在 table 3他们正在观看的 channel 类型，并根据观看某个 channel 类型的所有用户进行计数。

channel 类型还有:sport, shopping, entertainment and so on 。

Answer 1

你也可以尝试这个:

select count(*) from t1 join t2 on t1.id = t2.id and t2.userid like '130%' join 
t3 on t1.channel = t3.channel group by t1.channel

sql fiddle here

编辑:

另一个版本:

select  count(t1.id) , t1.*, t2.*, t3.*
from t1 join t2 on t1.id = t2.id  join 
t3 on t1.channel = t3.channel and t2.userid 
like '130%' group by t3.channel, t1.id

fiddle