sql - 计算其他表中的值

Question

我有两个表 = subjects 和 stats。

  -- subjects --             
------------------          
|  id  | name    |          
------------------         
|  1  |  subjecta |        
|  2  |  subjectb |       
|  3  |  subjectc |         
|  4  |  subjectd |         
|  5  |  subjecte |        
|  6  |  subjectf |        
|  7  |  subjectg |      
|  8  |  subjecth |         
|  9  |  subjecte |       
| ... |  subjectf |       
------------------       

-- stats --
-----------------------------------
| user_id | subject_id | correct  |
-----------------------------------
|  1      |    1      |   false  |
|  1      |    1      |   false  | 
|  1      |    2      |   false  |
|  4      |    3      |   false  |
|  4      |    4      |   false  |
|  4      |    5      |   false  |
|  2      |    1      |    true  |
|  2      |    1      |    true  |
|  2      |    2      |    false |
|  2      |    2      |    true  |
|  2      |    3      |    false |
---------------------------------

我需要什么，例如给定 user_id(如 2)，以获取所有主题(来自主题表)和他所做的(来自统计)正确的计数/false 像这样:

--------------------------------------------------
|  id  | name     |  correct true | correct false|
----------------------------------|----------------  
|  1  |  subjecta |       2       |       0      |
|  2  |  subjectb |       1       |       1      |  
|  3  |  subjectc |       0       |       1      |  
|  4  |  subjectd |       0       |       0      | 
|  5  |  subjecte |       0       |       0      | 
|  6  |  subjectf |       0       |       0      | 
|  7  |  subjectg |       0       |       0      | 
|  8  |  subjecth |       0       |       0      | 
|  9  |  subjecte |       0       |       0      | 
| ... |  subjectf |       0       |       0      | 
----------------------------------|--------------|

我不知道该怎么做。

Answer 1

您可以通过连接两个表以及一些条件聚合来计算正确答案和错误答案的数量来实现这一点。

SELECT
    t1.id,
    t1.name,
    SUM(CASE WHEN t2.correct = 'true'  THEN 1 ELSE 0 END) AS correct_true,
    SUM(CASE WHEN t2.correct = 'false' THEN 1 ELSE 0 END) AS correct_false
FROM subjects t1
LEFT JOIN stats t2
    ON t1.id = t2.subject_id AND
       t2.user_id = 2
GROUP BY t1.id, t1.name