mysql - 从同一张表中获取记录数时如何编写mysql innerquery

Question

从 2 个表中获取结果的查询:

SELECT path.* FROM (SELECT  tbc.course_name
slp.course_id,slp.student_type,slp.stu_reference_id,
count(slp.course_id) as counttotalWatchedStudents
 from tbl_student_learning_path  slp LEFT JOIN tbl_courses tbc
 on tbc.course_pid = slp.course_id WHERE slp.stu_reference_id =34 
and slp.student_type='institute' GROUP BY slp.course_id ) as path 

查询结果表:

| course_id                     | totalCollegeStudents | fullwatchedStudentsCount | counttotalWatchedStudents | sumOfWatchPointsForWatchedStudents | totalStudentsAvg | fullwatchedAvg |
|-------------------------------|----------------------|--------------------------|---------------------------|------------------------------------|------------------|----------------|
| Number Systems                | 9                    | 0                        | 3                         | 60                                 | 20.0000          | 0              |
| Percentages                   | 9                    | 0                        | 3                         | 30                                 | 10.0000          | 0              |
| Blood Relations               | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |
| Calandar                      | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |
| Percentages                   | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |
| Permutation & Combination | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |
| Probability                   | 9                    | 0                        | 3                         | 90                                 | 30.0000          | 0              |
| Ratios                        | 9                    | 0                        | 3                         | 120                                | 40.0000          | 0              |
| Time and Work                 | 9                    | 0                        | 3                         | 150                                | 50.0000          | 0              |
| Time Speed & Distance     | 9                    | 1                        | 3                         | 140                                | 46.6667          | 100            |
| Averages                      | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |
| Coding and Decoding           | 9                    | 3                        | 3                         | 300                                | 100.0000         | 300            |

从上表中，我想在主查询中添加内部查询查询应该是这样的:

( select count(t1.watched_percentage) from tbl_student_learning_path t1 
WHERE t1.stu_reference_id =34 and t1.student_type='institute' 
AND t1.watched_percentage >= 90 group by t1.course_id  )
fullwatchedStudentsCount,

结果应该是这样的(这没什么，但是如果第一个表 counttotalWatchedStudents 的值为 3，则意味着有两种类型的人1.学生观看满(watched_percentage>=90)2.仍在观看的学生(watched_percentage 1-89) )

| fullwatchedStudentsCount | fullwatchedStudentsSum |
|--------------------------|------------------------|
| 2                        | 200                    |
| 1                        | 100                    |
| 0                        | 0                      |
| 2                        | 200                    |
| 1                        | 100                    |
| 1                        | 100                    |
| 0                        | 0                      |
| 2                        | 200                    |
| 2                        | 200                    |
| 1                        | 100                    |
| 2                        | 200                    |
| 1                        | 100                    |

Answer 1

如果我没理解错的话，你想把看到90%以上的学生汇总到另一列:

我会这样做:

SELECT tbc.course_name,
       slp.course_id,
       slp.student_type,
       slp.stu_reference_id,
       count(slp.course_id) AS counttotalWatchedStudents,
       sum(if(slp.watched_percentage>=90, 1, 0)) AS fullwatchedStudentsCount
       sum(if(slp.watched_percentage>=90, watched_percentage, 0)) AS fullwatchedStudentsSum
FROM tbl_student_learning_path slp
LEFT JOIN tbl_courses tbc ON tbc.course_pid = slp.course_id
WHERE slp.stu_reference_id =34
    AND slp.student_type='institute'
GROUP BY slp.course_id
enter code here

希望对你有帮助