gpt4 book ai didi

MySQL计算多个外键

转载 作者:行者123 更新时间:2023-11-29 04:58:00 24 4
gpt4 key购买 nike

所以我有一个查询,我从“站点”表中选择,并使用外键计算“页面”的数量

SELECT s_id, s_name, s_main_url, COUNT(p_id) AS numpages
FROM sites
INNER JOIN pages ON sites.s_id = pages.site_id
GROUP BY pages.site_id
ORDER BY s_id ASC

我想添加另一个外键来计数,可能不止一个,所以我尝试了以下

SELECT s_id, s_name, s_main_url, COUNT(p_id) AS numpages, COUNT(l_id) AS numlinks
FROM sites
INNER JOIN pages ON sites.s_id = pages.site_id
INNER JOIN links ON sites.s_id = links.site_id
GROUP BY pages.site_id, links.site_id
ORDER BY s_id ASC

numpages 和 numlinks 的计数是相同的,它们似乎将所有表的结果相乘,给我一些不正确的巨大数字

有什么想法吗?

最佳答案

您应该考虑使用子选择而不是 JOIN 和 GROUP BY。

SELECT s_id, s_name, s_main_url, numpages, numlinks
FROM sites,
(SELECT COUNT(p_id) as numpages FROM pages WHERE pages.site_id = sites.s_id) as cnt_pages,
(SELECT COUNT(l_id) as numlink FROM links WHERE links.site_id = sites.s_id) as cnt_links
ORDER BY s_id ASC

关于MySQL计算多个外键,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/4659603/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com