<pre><code>.headers on
.mode column
.width 1 8 8 8
with
data as (
select 1 n,'name1' name, 'group1' groupcol, 10000 v union
select 2 n,'name2' name, 'group1' groupcol, 12000 v union
select 3 n,'name3' name, 'group2' groupcol, 9000 v union
select 4 n,'group1' name, null groupcol, 40000 v union
select 5 n,'name4' name, 'group2' groupcol, 30000 v union
select 6 n,'name5' name, null groupcol, 11000 v union
select 7 n,'group2' name, null groupcol, 1000 v union
select 8 n,'name6' name, 'group1' groupcol, 19000 v
),
data2 as (select ifnull(groupcol,name) groupcol,* from data)
select
n,
name,
groupcol,
(select max(v) from data2 where groupcol=d.groupcol) maxv
from data2 d
order by n;
</code></pre>
<p>结果:</p>
<pre><code>n name groupnum maxv
-
1 name1 group1 40000
2 name2 group1 40000
3 name3 group2 30000
4 group1 group1 40000
5 name4 group2 30000
6 name5 name5 11000
7 group2 group2 30000
8 name6 group1 40000
</code></pre>
<p>您需要SQLite版本3.8.3(2014)或更新版本才能理解WITH子句。如果您使用的是较旧的版本,那么sql很容易重写,只需在后面使用带子选择的圆括号即可</p>