在join表中使用sql SUM来获得某个结果
在join表中使用sql SUM来获得某个结果
提问于 2017-11-08 23:26:59
我有4个这样的表：
问题：
+----+------------+
| id | title |
+----+------------+
| 1 | Question A |
| 2 | Question B |
| 3 | Question C |
| 4 | Question D |
| 5 | Question E |
| 6 | Question F |
| 7 | Question G |
| 8 | Question H |
| 9 | Question J |
| 10 | Question K |
+----+------------+ANSWERED_QUESTIONS
+----+--------+-------------+-----------+
| ID | value | question_id | answer_id |
+----+--------+-------------+-----------+
| 1 | YES | 1 | 1 |
| 2 | NO | 2 | 1 |
| 3 | NO | 3 | 1 |
| 4 | YES | 4 | 1 |
| 5 | YES | 5 | 1 |
| 6 | YES | 6 | 1 |
| 7 | YES | 7 | 1 |
| 8 | NO | 8 | 1 |
| 9 | YES | 9 | 1 |
| 10 | NO | 10 | 1 |
| 11 | NO | 1 | 2 |
| 12 | YES | 2 | 2 |
| 13 | YES | 3 | 2 |
| 14 | YES | 4 | 2 |
| 15 | YES | 5 | 2 |
| 16 | YES | 6 | 2 |
| 17 | YES | 7 | 2 |
| 18 | YES | 8 | 2 |
| 19 | NO | 9 | 2 |
| 20 | YES | 10 | 2 |
+----+--------+-------------+-----------+答案
+----+-----------+----------+-------------+
| ID | total_yes | total_no | hospital_id |
+----+-----------+----------+-------------+
| 1 | 6 | 4 | 1 |
| 2 | 8 | 2 | 2 |
+----+-----------+----------+-------------+医院
+----+------------+
| ID | name |
+----+------------+
| 1 | Hospital A |
| 2 | Hospital B |
| 3 | Hospital C |
+----+------------+我想要得到医院回答每个问题的total_yes和total_no，例如
FINAL_TABLE
+----+------------+-----------+----------+-------------+
| id | Question | Total Yes | Total No | Hospital_id |
+----+------------+-----------+----------+-------------+
| 1 | Question A | 1 | 1 | 1 |
| 2 | Question B | 1 | 1 | 1 |
| 3 | Question C | 1 | 1 | 1 |
| 4 | Question D | 2 | 0 | 1 |
+----+------------+-----------+----------+-------------+我需要使用此信息创建一个图表，用户将在其中放置医院名称，并将显示问题详细信息
我该怎么做呢？
我在postgres数据库中使用Rails
编辑1
问题模型
class Question < ApplicationRecord
has_many :answered_questions, inverse_of: :question
has_many :answers, through: :answered_questions
has_attached_file :image, :storage => :cloudinary, :path => ':id/:style/:filename', styles: { medium: "300x300>", thumb: "100x100>" }
validates_attachment_content_type :image, content_type: /\Aimage\/.*\z/
validates_presence_of :title, :campaign
# validates_associated :campaign
def image_url
image.url(:thumb)
end
end回答 2
Stack Overflow用户
发布于 2017-11-08 23:51:49
Question.joins(:answers).select('title, total_yes, total_no, hospital_id'). distinct这就是我得到你的任务的方式。如果它不工作或它是无效的解决方案，请随时通知我，我将更新并尝试帮助。
@Eduardorph或者total_yes和total_no是专门针对医院的，没有考虑问题吗？那么为什么要将其存储在answers表中，其中应该有很多针对每个医院的记录，并且您的合计列将只是每个相似医院id的重复？
你的数据库设计中有严重的问题。我95%确定question_answers和answer应该在一个表中被压缩。
我也不喜欢你的total_yes专栏。您应该考虑counter_cache解决方案，或者每次都使用SQL语言进行计算。现在，我确信这段代码会变成将来的bug。
Stack Overflow用户
发布于 2017-11-09 00:45:22
假设answer_id始终等于hospital_id，则您可能需要以下查询：
SELECT * FROM
(
SELECT
aq.question_id,
q.title,
COUNT(*) FILTER(WHERE value='YES') as total_yes,
COUNT(*) FILTER(WHERE value='NO') as total_no,answer_id
FROM answered_questions aq,questions q
WHERE aq.question_id = q.id
GROUP BY ROLLUP(question_id,title,answer_id)
)data_set
WHERE answer_id IS NOT NULL
ORDER BY answer_id,question_id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/47183543
复制相关文章
相似问题
腾讯云开发者
