如果表中不存在MySQL，则强制分组以返回NULL值

Question

希望大家都做得好，我确实有一个关于mysql的小问题，因为我在查询方面没有那么多的经验，所以请帮我解决下面的查询，我有一个标记表有列按照下表，我想要一个输出，按考试类型分组，如果表中没有group by记录，则返回空值。

subject       examtype     fullmark    secured
SCIENCE       CLS          50          20 
SCIENCE       CLS          50          50
MATHEMATICS   MNT          50          10
MATHEMATICS   MNT          50          35
MATHEMATICS   MNT          100         85
SCIENCE       MCK          100         89 
MATHEMATICS   CMP          100         72
ENGLISH       PST          50          33

我现在的查询如下

SELECT round((SUM(`secured`)/SUM(`fullmark`))*100) as percent,subject,examtype FROM tbl_marks WHERE subject = 'MATHEMATICS' GROUP BY examtype

上面的查询返回下面的结果

    percent   subject      examtype
    72        MATHEMATICS  CMP
    65        MATHEMATICS  MNT

我希望我的输出如下

percent   subject      examtype
72        MATHEMATICS  CMP
65        MATHEMATICS  MNT
0         MATHEMATICS  CLS
0         MATHEMATICS  MCK
0         MATHEMATICS  PST

如何查询请帮助我，提前谢谢。

Answer 1

这个查询创建了subject和examtypes的每个组合，然后使用一个右外部连接来引入在核心查询中看不到的组合。

SELECT
  coalesce(round((SUM(secured)/SUM(fullmark))*100), 0) as percent,
  all_subjects_examtypes.subject,
  all_subjects_examtypes.examtype
from
  tbl_marks
  right outer join (
    select distinct
      s.subject,
      e.examtype
    from
      tbl_marks as s
      cross join tbl_marks as e
  ) as all_subjects_examtypes on
    tbl_marks.subject = all_subjects_examtypes.subject and
    tbl_marks.examtype = all_subjects_examtypes.examtype
where
  all_subjects_examtypes.subject = 'MATHEMATICS'
group by
  all_subjects_examtypes.examtype

Answer 2

您的表中没有包含这些检查类型的记录，因此您必须制造一个包含这些检查类型的记录的记录集。

您可以通过将不同的主题与不同的examtypes交叉连接来获得该记录集：

SELECT dsubject.subject, dexamptype.examtype
        FROM (SELECT DISTINCT subject FROM tbl_marks) dsubject,
            (SELECT DISTINCT examptype FROM tbl_marks) dexamptype

现在，您已经为这些科目的每个科目和每种考试类型设置了一个记录

您可以在查询中使用它，如下所示：

SELECT
    round((SUM(tbl_marks.`secured`)/SUM(tbl_marks.`fullmark`))*100) as percent
    subject_examtype.subject,
    subject_examtype.examtype   
FROM
    (
        SELECT dsubject.subject, dexamptype.examtype
        FROM (SELECT DISTINCT subject FROM tbl_marks WHERE subject = 'MATHEMATICS') dsubject,
            (SELECT DISTINCT examptype FROM tbl_marks) dexamptype
    ) subject_examtype
    LEFT OUTER JOIN tbl_marks 
        ON subject_examtype.subject = tbl_marks.subject
            AND subject_examptype.examtype = subject.examtype
GROUP BY subject_examtype.subject, subject_examtype.examtype;

因为这只是你追求的一个主题，所以这将是相同的：

SELECT
    round((SUM(tbl_marks.`secured`)/SUM(tbl_marks.`fullmark`))*100) as percent
    subject_examtype.subject,
    subject_examtype.examtype   
FROM
    (SELECT DISTINCT 'MATHEMATICS' as subject, examtype FROM tbl_marks) subject_examtype
    LEFT OUTER JOIN tbl_marks 
        ON subject_examtype.subject = tbl_marks.subject
            AND subject_examptype.examtype = subject.examtype
GROUP BY subject_examtype.subject, subject_examtype.examtype;

但是，如果您想对更多的主题执行此操作，则查询的第一个版本更有意义。