擅长:python、mysql、java
<p>显然,大多数查询的目的只是将结果按组排序。这可以简单地按顺序完成。你知道吗</p>
<p>可以通过<a href="https://en.wikipedia.org/wiki/Correlated_subquery" rel="nofollow">correlated subquery</a>获得每个菌株的最新日期:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT NumLabeledCarbons, MDV
FROM LCMS
WHERE StrainId IN ('U','S','UG','SG')
AND date(DateTime) = (SELECT max(date(DateTime))
FROM LCMS AS L2
WHERE L2.StrainId = LCMS.StrainId)
AND HarvestIndex = (SELECT min(HarvestIndex)
FROM LCMS)
ORDER BY StrainId, FlaskNum, Species;
</code></pre>
<p>但是,这将为表中的每一行重新执行子查询。
在一个单独的子查询中计算四个菌株的最新日期,并将其连接回原始表,可能更有效:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT NumLabeledCarbons, MDV
FROM LCMS
JOIN (SELECT StrainId,
max(DateTime) AS MaxDate
FROM LCMS
WHERE StrainId IN ('U','S','UG','SG')
GROUP BY StrainId) AS MD
ON LCMS.StrainId = MD.StrainId AND
date(LCMS.DateTime) = MD.MaxDate
WHERE HarvestIndex = (SELECT min(HarvestIndex)
FROM LCMS)
ORDER BY StrainId, FlaskNum, Species;
</code></pre>