连接3个表并选择count

nhhxz33t 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(365)

我有3个表{网站}{账户}{广告}
网站表

id     title    url           etc
-----------------------------------
1      site1    site1.com     ...
2      site2    site1.com     ...
3      site3    site3.com     ...

帐户表

id     websiteID   username    etc
-----------------------------------
1      1           username1   ...
2      2           username2   ...
3      1           username1   ...
4      3           username5   ...

ads表

id     accountID   title    etc
---------------------------------
1      1           title1   ...
2      2           title1   ...
3      1           title3   ...
5      4           title4   ...

我想从网站表开始加入这3个表，从网站表中得到一些数据，和它的网站相关的帐户数，和它的帐户相关的广告数。我还希望计数结果为零或空。帐户表中的用户名不是唯一的，可以相同。ads表中的标题也不是唯一的，可以是相同的。
这是我的查询，但有时它返回计数错误的结果！

SELECT 
    websites.id as website_id
,   websites.title as website_title
,   COUNT(accounts.websiteID) as accounts_count
,   COUNT(ads.accountID) as ads_count
,   ads.lastUpdate
,   websites.activation as website_activation 
FROM websites 
LEFT JOIN accounts 
    ON websites.id = accounts.websiteID 
LEFT JOIN ads 
    ON accounts.id = ads.accountID
GROUP BY websites.id;

你能帮我吗{
我想在这样的表格中显示这个结果：

website_title     accounts_count   ads_count    last update  operations
-------------------------------------------------------------------------
website1           3               8            2017/07/27   etc...
website2           0               0            2017/07/27   etc...
website3           3               9            2017/07/27   etc...
website4           5               15           2017/07/27   etc...

sql mysql count database-design JoinTable

来源：https://stackoverflow.com/questions/51552441/join-3-tables-and-select-count

3条答案

按热度按时间

3lxsmp7m1#

似乎计数需要改变。
而ads.lastupdate的最大值会更准确。
f、 e。

SELECT 
    websites.id as website_id
,   websites.title as website_title
,   COUNT(DISTINCT accounts.ID) as accounts_count
,   COUNT(ads.ID) as ads_count
,   MAX(ads.lastUpdate) as LastUpdateAds
,   websites.activation as website_activation 
FROM websites 
LEFT JOIN accounts 
    ON websites.id = accounts.websiteID 
LEFT JOIN ads 
    ON accounts.id = ads.accountID
GROUP BY websites.id;

赞(0）回复(0）举报 2021-06-21

y53ybaqx2#

SELECT websites.id as website_id, websites.title as website_title,  
   ads.lastUpdate, websites.activation as website_activation 
   COUNT(accounts.websiteID) as accounts_count, COUNT(ads.accountID) as ads_count, 
FROM websites, accounts, ads
WHERE websites.id = accounts.websiteID 
   AND accounts.id = ads.accountID;

赞(0）回复(0）举报 2021-06-21

flmtquvp3#

关键是当你加入 websites 在帐户中，每行对应一行 accounts . 当你加入 ads 将行计数进一步乘法。我猜你现在也得到了同样的计数 accounts_count 以及 ads_count . 而且你还有 lastUpdate 列自 ads 表和带有agregate函数的。

赞(0）回复(0）举报 2021-06-20

我来回答

连接3个表并选择count

3条答案

相关问题

热门标签

最新问答