关闭

关于如何只取得每组数据中的一个最大值的问题?

标签: inserttable
456人阅读 评论(0) 收藏 举报
分类:

 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相同的每组数据日期最大值:

select top 5 *
from topic as tmp
where not exists(select * from topic where boardid=tmp.boardid and addtime>tmp.addtime) order by addtime desc
--result 相同boardid,addtime最大的数据
Topicid     title      boardid     addtime                                               
----------- ---------- ----------- ------------------------------------------------------
10          zti        103         2007-01-10 00:00:00.000
9           zqw        105         2007-01-09 00:00:00.000
8           zww        104         2007-01-08 00:00:00.000
7           xcv        102         2007-01-07 00:00:00.000
5           jhv        100         2007-01-05 00:00:00.000

(所影响的行数为
5 行)

 

select a.* from topic a,
(
  select boardid,max(addtime)as addtime from topic group by boardid
)b
where a.boardid=b.boardid and a.addtime=b.addtime
order by a.boardid

--result 相同boardid,addtime最大的数据
Topicid     title      boardid     addtime       

5              jhv          100          2007-01-05 00:00:00.000
2              era         101          2007-01-02 00:00:00.000
7              xcv          102          2007-01-07 00:00:00.000
10            zti           103          2007-01-10 00:00:00.000
8              zww       104          2007-01-08 00:00:00.000
9              zqw        105          2007-01-09 00:00:00.000

 


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:9348次
    • 积分:162
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:11篇
    • 译文:0篇
    • 评论:3条
    文章分类
    文章存档
    最新评论