Sqlite 分组查询，Sqlite分组

和通数据库htsjk.Com2019-08-14 05:26 来源:未知阅读:8081 评论 373 热度5

标签：

Sqlite 分组查询，Sqlite分组

准备测试表：

CREATE TABLE [album] (
  [_id] INTEGER PRIMARY KEY AUTOINCREMENT, 
  [age] integer, 
  [name] text, 
  [length] integer default 0);

准备数据：

insert into [album] ([age], [name], [length]) values (15, 'A', 175);
insert into [album] ([age], [name], [length]) values (16, 'B', 125);
insert into [album] ([age], [name], [length]) values (12, 'F', 150);
insert into [album] ([age], [name], [length]) values (19, 'C', 140);
insert into [album] ([age], [name], [length]) values (30, 'C', 160);
insert into [album] ([age], [name], [length]) values (19, 'D', 170);
insert into [album] ([age], [name], [length]) values (21, 'E', 190);
insert into [album] ([age], [name], [length]) values (18, 'F', 165);

1. 查询以name分组，每组的记录条数，以及每组中age最大的那个

select count([_id]), max([age])  from [album] group by [name]

2.查询以name分组，每组的记录条数，以及每组中最大age对应的length

select count([_id]), [length] from [album] group by [name] having [age]= max([age])