使用 3 个条件的 SQL LOGIC

Question

course_completions CC
        id  coursemodid userid  state   timemodified
        370     23       2         1    1433582890  
        329     24       89        1    1427771915
        333     30       39        1    1428309816
        332     32       39        1    1428303307
        327     33       40        1    1427689703
        328     34       89        1    1427710711
        303     35       41        1    1410258482
        358     36       99        1    1432020067
        365     25       2         1    1433142455
        304     26       69        1    1410717866
        353     37       95        1    1430387005
        416     38       2         1    1438972465
        300     27       70        1    1409824001
        302     29       74        1    1412055704
        297     30       2         1    1409582123
        301     133      41        1    1410255923
        336     133      91        1    1428398435
        364     133      40        1    1433142348
        312     133      85        1    1425863621

        course_modules CM
        id    course    
        23      6   
        24      6   
        25      6    
        26      6    
        27      6    
        28      6    
        29      8    
        30      8    
        31      8    
        32      8    
        33      8   
        34      5   
        35      5    
        36      5   
        37      5   
        38      5    
        39      9    
        40      9    
        41      9    

        course_mod_settings CMS
        id  course  modinstance     
        27    8         30                  
        28    8         31                  
        29    8         32                  
        30    8         33                  
        31    6         23                  
        32    6         24                  
        33    6         25                  
        34    6         26                  
        35    6         27                  
        36    6         28                  
        37    9         39                  
        38    9         40                  
        39    9         41

我需要每个用户的计数，每门类(class)都有已完成的模块、进程中的模块和未启动的模块，其中通过从表 CM 中获取 courseia 从表 CC 中获取用户 ID 的计数，获取用户从每门类(class)中完成的模块数。

(一门类(class)可以有多个模块，并且一门类(class)可以有很多用户尝试了所有模块、少数模块或根本没有尝试)。

所以，我需要用户数量——已经完成了模块数量——在一门类(class)中。 (3个逻辑)

已完成。用户表示 :如果尝试的模块数等于表 CMS 中的模块数(例如:用户每门类(class)尝试的模块数 = 9，no.modinstance = 9。因为 7 不等于 9，所以它们已完成。)

Inprocess.Users 表示 :尝试的模块数应大于 0，但不等于 [count(modinstance) per course](例如:用户每门类(class)尝试的模块数 = 7，no.modinstance = 9。因为 7 不等于9，它们是进程中的。)

Notstarted.Users 表示 :尝试的模块数应等于 0，(例如:用户每门类(class)尝试的模块数 = 0。它们未启动)。

    OUTPUT :
Course   No.Completed.Users   No.Inprocess.Users    No.Notstarted.Users
 5              65                 32                       6
 6              40                 12                       15
 8              43                 56                       0
 9               0                 7                        9

先生，这是我正在尝试的一个非常关键的逻辑，我无法找到解决方案。我希望stackoverflow开发人员可以帮助我。我尝试了我的查询:

SELECT cm.course AS "Course",

(CASE WHEN 
(SELECT count(cms.id) FROM course_mod_settings cms) = 
(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 )
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Completed.Users",

(CASE WHEN 
(SELECT count(cms.id) FROM course_mod_settings cms) > 0 AND 
(SELECT count(cms.id) FROM course_mod_settings cms) !=
(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 )
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Inprocess.Users",

(CASE WHEN 
(SELECT count(cms.id) FROM course_mod_settings cms) = 0
THEN COUNT(SELECT count(cmc.coursemodid) FROM course_completions cc JOIN course_modules cm ON cmc.coursemodid = cm.id WHERE cmc.state=1 ) END) AS "No.Notstarted.Users"

FROM 
mdl_course c 
GROUP BY c.id

Answer 1

SQL Fiddle

SELECT course AS "Course",
       SUM(CASE WHEN completion_count = module_count THEN 1 ELSE 0 END) AS "No.Completed.Users",
       SUM(CASE WHEN completion_count > 0 AND completion_count < module_count THEN 1 ELSE 0 END) AS "No.Inprocess.Users",
       SUM(CASE WHEN completion_count = 0 THEN 1 ELSE 0 END) AS "No.Notstarted.Users"
  FROM (SELECT course, COUNT(*) AS module_count
          FROM course_modules cm 
         GROUP BY course) course_module_counts JOIN
       (SELECT cm.course AS courseid, users.id AS userid, SUM(CASE WHEN cc.state = 1 THEN 1 ELSE 0 END) completion_count
          FROM ((SELECT DISTINCT userid AS id FROM course_completions) users CROSS JOIN course_modules cm) LEFT JOIN course_completions cc ON users.id = cc.userid AND cc.coursemodid = cm.id
         GROUP BY cm.course, users.id) course_completion_counts
       ON course_module_counts.course = course_completion_counts.courseid
 GROUP BY course

给出此输出，它与您在问题中提供的有限数据集相匹配。

| course | No.Completed.Users | No.Inprocess.Users | No.Notstarted.Users |
|--------|--------------------|--------------------|---------------------|
|      5 |                  0 |                  5 |                   7 |
|      6 |                  0 |                  4 |                   8 |
|      8 |                  0 |                  4 |                   8 |
|      9 |                  0 |                  0 |                  12 |