擅长:python、mysql、java
<p>考虑以下SQL聚合查询:</p>
<pre><code>import sqlite3 as lite
con = lite.connect('fluxData.db')
sql = "SELECT StrainId, FlaskNum, Species, NumLabeledCarbons, MDV, \
Max(date(DateTime)) As LatestExperimentDate, Min(HarvestIndex) As EarliestHarvest \
FROM LCMS \
WHERE StrainId IN ('U','S','UG','SG') \
GROUP BY StrainId, FlaskNum, Species, NumLabeledCarbons, MDV \
HAVING DateTime = Max(date(DateTime)) AND HarvestIndex = Min(HarvestIndex) \
ORDER BY NumLabeledCarbons;"
with con:
cur = con.cursor()
cur.execute(sql)
for data in cur.fetchall():
...
</code></pre>
<p>然后可以将<code>Avg()</code>、<code>Sum()</code>和<code>Count()</code>添加到<code>SELECT</code>行中的任何数值列中,以便按组进行计算。你知道吗</p>