时间戳

首页 > 解决方案 > 如何计算 3 个表的结果

mysql - 如何计算 3 个表的结果

问题描述

请看一下这三个表：

Pets
+----+---------+-------+
| id | petname | owner |
+====+=========+=======+
| 1  | chew    | 1     |
+----+---------+-------+
| 2  | yo      | 2     |
+----+---------+-------+
| 3  | mah     | 3     |
+----+---------+-------+

Owners
+----+-------+-----------+
| id | store | ownername |
+====+=======+===========+
| 1  | 1     | Jonh      |
+----+-------+-----------+
| 2  | 2     | Joe       |
+----+-------+-----------+
| 3  | 3     | Smith     |
+----+-------+-----------+

Stores
+----+------------+
| id | storename  |
+====+============+
| 1  | Lite Store |
+----+------------+
| 2  | Mega       |
+----+------------+
| 3  | Corner     |
+----+------------+

这有可能得到这个结果吗？

+------------+------------+
| storename  | Total Pets |
+============+============+
| Lite Store | 5          |
+------------+------------+
| Mega       | 8          |
+------------+------------+
| Corner     | 0          |
+------------+------------+

我尝试了几个小时很多子查询和连接，但我错过了一些东西，也许是联合？

在下面我接近了，但仍然很远

SELECT storename, COUNT(distinct stores.storename) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT stores.storename, COUNT(distinct stores.storename) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT stores.storename, COUNT(distinct owners.id) as store, count(DISTINCT pets.owner) as petowner from stores inner join owners on owners.id = stores.id inner JOIN pets on pets.owner = stores.id group by stores.id
SELECT COUNT(*),(SELECT COUNT(*) from stores) FROM pets
SELECT COUNT(*),(SELECT DISTINCT(COUNT(*)) from stores),(SELECT DISTINCT(COUNT(*)) FROM owners) FROM pets
SELECT DISTINCT(COUNT(*)), ( select count(DISTINCT(stores.storename)) from stores join owners on stores.id = stores.storename ) FROM pets
select stores.storename, (select count(*) from pets) from stores join owners on stores.id = stores.storename group by storename
select DISTINCT(stores.storename), (select count(*) from pets) from stores join owners on stores.id = stores.storename group by storename
select (count(stores.storename)), (select count(*) from pets) as total from stores join owners on stores.id = stores.storename group by storename

有没有办法得到上面的结果？

任何帮助将不胜感激！

标签： mysqljoinsubqueryunion

解决方案

感谢您提供您的查询。我已经形成了以下查询。请您尝试一下，看看它是否有效。如果没有，请告诉我

Select s.storename, count(p.petname) from pets p join owners o on p.owner=o.id join stores s on o.store=s.id group by 1

推荐阅读