统计数据库中的平均年龄，合并2个查询 - Count average age from database, merge 2 queries

Count average age from database, merge 2 queries

好的，所以我不认为这个问题像标题所说的那么简单，但这里是。。。我有这个问题：

SELECT age.*, job.id FROM age, job WHERE ((age.aid = job.id) AND (age.aid='$id'))

这基本上连接了两个表age和job，并得到了匹配的行

但现在我需要在表年龄下找到存储在行猜测中的最常见年龄

数据库布局

表：年龄

行：aid-猜测

表：作业

行：id

根据我的搜索结果，我需要使用count（）

例如：

SELECT guess, COUNT(guess) AS countguess FROM age GROUP BY guess ORDER BY countguess DESC LIMIT 1

但是如何将这两个查询合并在一起呢？

您可以执行类似的操作

SELECT age.aid, job.id, age.guess, COUNT(age.guess) AS countguess 
FROM age, job 
WHERE ((age.aid = job.id) AND (age.aid='$id')) 
GROUP BY guess 
ORDER BY countguess DESC

当你说合并时，我认为你的意思是加入他们。当两个表之间存在关系时，可以加入。

您可以使用UNION ALL来完成此

SELECT aid, id, guess, SUM(countguess ) AS countguess
FROM
(
 SELECT age.aid, job.id, guess, 0 AS countguess  
 FROM age
      INNER JOIN job
          ON age.aid = job.id AND 
             age.aid='$id'
 UNION ALL
 SELECT age.aid, 0 AS job.id, guess, COUNT(guess) AS countguess 
 FROM age 
 GROUP BY guess 
 ORDER BY countguess DESC 
 LIMIT 1
) a
GROUP BY aid

*注意：UNION ALL在两个查询中都需要相同数量的列

SELECT age.*, job.id FROM age, job,
    (SELECT AVG(guess) AS countguess FROM age GROUP BY guess ORDER BY countguess DESC LIMIT 1) AS countguess
FROM job AS j
INNER JOIN age AS a
ON j.id = a.aid
WHERE a.aid = '{$id}'

我不确定这是否是你想要的？也许

SELECT age.*, job.id FROM age, job,
    (SELECT AVG(guess) AS countguess FROM age WHERE guess = a.guess GROUP BY '1') AS countguess
FROM job AS j
INNER JOIN age AS a
ON j.id = a.aid
WHERE a.aid = '{$id}'