擅长:python、mysql、java
<p>最常用的SQL方法是使用单个SQL查询</p>
<p>可以使用<a href="http://www.sqlite.org/lang_expr.html#case" rel="nofollow noreferrer">CASE expression</a>和<a href="http://www.sqlite.org/lang_expr.html#cosub" rel="nofollow noreferrer">correlated subquery</a>计算两个不同的值:</p>
<pre class="lang-sql prettyprint-override"><code>SELECT Name,
CASE WHEN GroupColumn IS NOT NULL
THEN GroupColumn
ELSE Name
END AS "Group",
(SELECT max(Value)
FROM MyTable AS T2
WHERE T2.GroupColumn = MyTable.GroupColumn
OR T2.Name = MyTable.GroupColumn
) AS "Max(V)"
FROM MyTable;
</code></pre>
<p>(但是有一个<a href="http://www.sqlite.org/lang_corefunc.html#ifnull" rel="nofollow noreferrer">helper function</a>来替换空值;<code>GroupColumn</code>的整个表达式可以简化为<code>ifnull(GroupColumn, Name)</code>。)</p>