請看一下這 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
有沒有辦法得到上面的結果？
任何幫助將不勝感激！
uj5u.com熱心網友回復：
感謝您提供您的查詢。我已經形成了以下查詢。要求您嘗試一下，看看它是否有效。如果沒有，請告訴我
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/315226.html
上一篇：您如何根據在任一表中具有具有不同欄位的主鍵的UNION2表？
下一篇：根據值獲取名稱列