mysql - 如何将来自不同表的 3 个选择语句合并到 1 个特定查询中？

Question

我可以为该表及其关系编写特定查询，但将它们合并为一个是我遇到的困难

select clients, count(*)
from clients
inner join themes
on clients.id = themes.client_id
group by clients

select themes.id
from themes
inner join videos
on videos.theme_id = themes.id


select distinct videos.user_id, 
from videos
inner join properties
on properties.user_id = videos.user_id
group by properties.user_id

基本上，我想计算一个客户的独立用户数量

关系是

一个客户有很多主题

一个主题有很多视频

一个视频只有一个属性

一个用户有很多属性

谢谢

Answer 1

计算客户端的唯一用户数量:

select clients, count(DISTINCT properties.user_id) as num_users
from clients
inner join themes on clients.id = themes.client_id
inner join videos on videos.theme_id = themes.id
inner join properties on properties.user_id = videos.user_id
group by clients.clients_id;

您也可以使用更短的查询:用户总是有属性(这是假设)，那么拥有视频的用户将出现在属性表中，然后就不必加入:

select clients, count(DISTINCT videos.user_id) as num_users
from clients
inner join themes on clients.id = themes.client_id
inner join videos on videos.theme_id = themes.id
group by clients.clients_id;