mysql如何在一个表中添加多个where子句

lx0bsm1f 于 2021-06-15 发布在 Mysql

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

我的表上有这个查询，它返回下面给出的表

SELECT sub.Subject_Name, count(sa.Attendance) AS Lectures_Attended FROM student_attendance sa
            INNER JOIN TeacherSubjects ts on (sa.TeacherSubject_ID = ts.TeacherSubject_ID)
            INNER JOIN Subjects sub on(ts.Subject_ID = sub.Subject_ID)
            where sa.Student_ID = 1 AND sub.Semester = '8th-Semester' AND sa.Attendance=1
            GROUP by sa.TeacherSubject_ID
UNION ALL

SELECT sub.Subject_Name, count(sa.Attendance) AS Total_Lectures FROM student_attendance sa
            INNER JOIN TeacherSubjects ts on (sa.TeacherSubject_ID = ts.TeacherSubject_ID)
            INNER JOIN Subjects sub on(ts.Subject_ID = sub.Subject_ID)
            where sa.Student_ID = 1 AND sub.Semester = '8th-Semester'
            GROUP by sa.TeacherSubject_ID

SubjectName LecturesAttended                                                     
    Php           2                                                             
    Php           3

现在我不需要第二行，即php | 3
我需要把table摆成这样

SubjectName  LecturesAttended  TotalLectures                              
   Php             2               3

mysql php

来源：https://stackoverflow.com/questions/53853706/mysql-how-to-add-multiple-where-clauses-to-a-single-table

1条答案

按热度按时间

p5cysglq1#

您只需将查询减少到：

SELECT 
    sub.Subject_Name, 
    COUNT(IF(sa.Attendance=1,1,NULL)) AS Lectures_Attended,
    COUNT(*) Total_Lectures
FROM student_attendance sa INNER JOIN TeacherSubjects ts ON (sa.TeacherSubject_ID = ts.TeacherSubject_ID)
INNER JOIN Subjects sub ON (ts.Subject_ID = sub.Subject_ID)
WHERE sa.Student_ID = 1 AND sub.Semester = '8th-Semester'
GROUP by sa.TeacherSubject_ID;

请参阅mysql if（）函数，了解if函数在mysql中的工作方式。

赞(0）回复(0）举报 2021-06-16

我来回答

mysql如何在一个表中添加多个where子句

1条答案

相关问题

热门标签

最新问答