编写MySQL子查询的更好方法

Question

我的MySQL数据库中有两个表：allele和locus。我想知道，对于一个给定的位点，有多少等位基因，有多少有状态Tentative。我现在有以下带有子查询的查询：

SELECT COUNT(*) as alleleCount,
     (SELECT COUNT(*) 
      FROM allele 
      INNER JOIN locus ON allele.LocusID = locus.PrimKey
      WHERE Status = 'Tentative'
      AND locus.ID = 762
      ) as newAlleleCount
FROM allele
INNER JOIN locus ON allele.LocusID = locus.PrimKey
WHERE locus.ID = 762

但是我觉得一定有更好的方法来写这个查询。

Answer 1

您可以使用SUM()使用sum with条件将得到布尔值1或0，因此它将给出条件的计数。

  SELECT locus.ID,COUNT(*) `all_alleles_per_locus`,
  SUM(Status = 'Tentative') `tentative_alleles_762`
  FROM allele 
  INNER JOIN locus ON allele.LocusID = locus.PrimKey
  GROUP BY locus.ID

Answer 2

一种方法是按其状态对轨迹进行分组，并获取每个状态的相应计数；使用WITH ROLLUP 修饰符将在最后添加一个NULL状态，表示总数：

SELECT   status, COUNT(*)
FROM     allele JOIN locus ON locus.PrimKey = allele.LocusID
WHERE    locus.ID = 762
GROUP BY status WITH ROLLUP

如果您绝对不想要一个包含所有状态的列表，则可以选择GROUP BY status = 'Tentative' (如果需要，可以选择WITH ROLLUP )-but，它将是不可接受的。