Question

请使用你熟悉的一种数据库，用SQL语句写出分组查询语句

Answer 1

有一张表如下:
create table topic
(
Topicid int,
title nvarchar(10),
boardid int,
addtime datetime
)
内容如下:
insert topic select
1, 'abc', 100, '2007-1-1'
union all select 2, 'era', 101, '2007-1-2'
union all select 3, 'avx', 102, '2007-1-3'
union all select 4, 'zcv', 100, '2007-1-4'
union all select 5, 'jhv', 100, '2007-1-5'
union all select 6, 'ztw', 103, '2007-1-6'
union all select 7, 'xcv', 102, '2007-1-7'
union all select 8, 'zww', 104, '2007-1-8'
union all select 9, 'zqw', 105, '2007-1-9'
union all select 10, 'zti', 103, '2007-1-10'
要求:按照boardid分组,按照datetime排序,取出前五条
语句:
select top 5 boardid from
(
select boardid,max(addtime) as addtime
from topic
group by boardid
) tmp order by addtime desc

解释:
1,group by 和 order by 不能同时使用,所以当要分组后再排序的时候就要嵌套(先分好组再排序)
2,但是在group by的时候,你要查询的字段必须是你分组的字段,如果group by 两个字段,分组时是按照两个字段同时相等的分组,因此达不到自己想要的结果.
但是使用一个字段分组,要查到两个字段就得:
select boardid,max(addtime) as addtime
from topic
group by boardid

Answer 2

SQL Server: select [字段名1],聚合函数([字段名2]) from [表名] group by [字段名1]