根据条件从多个表中获取数据

a9wyjsp7 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(343)

我需要从3个不同的mysql表中获取一些数据，如下所示。

+-----------------------------------+      
 | Questions                         |      
 -------------------------------------      
 | id: int(8)                        |      
 | -- data we don't care about here  |      
 | question: TEXT                    |      
 +-----------------------------------+    

 +--------------------------+               
 |Answers                   |               
 +--------------------------+               
 | id: int(8)               |               
 | -- other data            |               
 | answer: TEXT             |               
 +--------------------------+    

 +-----------------------------------+      
 |Votes                              |      
 +-----------------------------------+      
 | id: int(8)                        |      
 | step: tinyint(1)                  |      
 | answerId: int(8)                  |      
 +-----------------------------------+

我需要得到每个问题的票数最多的答案，以及这个答案的票数。
我能提出的最好的问题是：

SELECT question, answer, count(votes.id) votesCount FROM questions INNER JOIN answers ON questions.id = answers.questionId INNER JOIN votes ON answers.id = votes.answerId;

然而，这是错误的，因为它总是返回一个单一的结果与第一个问题，一个随机答案和总票数。

sql mysql window-functions count greatest-n-per-group

来源：https://stackoverflow.com/questions/62212084/get-data-from-multiple-tables-based-on-condition

1条答案

按热度按时间

smdnsysy1#

您当前的问题是您的查询缺少 group by 条款。所以它只返回一行，总票数和一个随机选择的问题和答案。
如果您运行的是mysql 8.0，那么使用aggregatin和window功能很容易做到这一点：

select question, answer, votescount
from (
    select 
        q.question, 
        a.answer, 
        count(*) votescount,
        rank() over(partition by q.id order by count(*) desc) rn
    from questions q
    inner join answers a on q.id = a.questionid 
    inner join votes on a.id = v.answerid
    group by q.id, q.question, a.id, a.answer
) t
where rn = 1

在早期的版本中，它要复杂一些。一个选项是使用 having 返回每组最大计数的子句：

select 
    q.question, 
    a.answer, 
    count(*) votescount,
    rank() over(partition by q.id order by count(*) desc) rn
from questions q
inner join answers a on q.id = a.questionid 
inner join votes on a.id = v.answerid
group by q.id, q.question, a.id, a.answer
having count(*) = (
    select count(*)
    from answers a1
    inner join votes v1 a1.id = v1.answerid
    where a1.questionid = q.id
    group by a1.id
    order by count(*) desc
    limit 1
)

赞(0）回复(0）举报 2021-07-26

我来回答

根据条件从多个表中获取数据

1条答案

相关问题

热门标签

最新问答