SQL语句分组获取记录的第一条数据

[wxy168]

我的表Demo的数据都是字符串类型，按照顺序的时间Time（也是字符串）排序的记录如下：

Num Name Time
1 a 2009/05/01
1 a 2009/05/02
1 a 2009/05/03
2 b 2009/05/04
2 b 2009/05/05
3 c 2009/05/06
3 c 2009/05/07
5 e 2009/05/08
1 a 2009/05/09
1 a 2009/05/10

我想输出类似按照Num分组的每组的第一条数据记录，比如上面的记录我想操作后得到如下记录：
Num Name Time
1 a 2009/05/01
2 b 2009/05/04
3 c 2009/05/06
5 e 2009/05/08
1 a 2009/05/09

这样的话用SQL语句怎么实现？？希望数据库操作高手指点下。谢谢～～～

[wxy168]

[Quote=引用 1 楼 josy 的回复:]
SQL codeselect
*
from
demo t
where
not exists(select 1 from demo where num=t.num and [time]<t.[time])
[/Quote]


是不是not exists(select top 1 from demo where num=t.num and [time]<t.[time])??
1是代表什么意思？？？

[you_tube]

declare @Tab table
(Num int, Name varchar(2),   Time DATETIME)
insert into @tab select 1    ,'a',        '2009/05/01'
insert into @tab select 1    ,'a',        '2009/05/02'
insert into @tab select 1    ,'a',        '2009/05/03'
insert into @tab select 2    ,'b',        '2009/05/04'
insert into @tab select 2    ,'b',        '2009/05/05'
insert into @tab select 3    ,'c',        '2009/05/06'
insert into @tab select 3    ,'c',        '2009/05/07'
insert into @tab select 5    ,'e',        '2009/05/08'
insert into @tab select 1    ,'a',        '2009/05/09'
insert into @tab select 1    ,'a',        '2009/05/10' 

select  * from @Tab t where  not exists(select 1 from @Tab where num=t.num and [time]<t.[time])

/*
Num         Name Time
----------- ---- -----------------------
1           a    2009-05-01 00:00:00.000
2           b    2009-05-04 00:00:00.000
3           c    2009-05-06 00:00:00.000
5           e    2009-05-08 00:00:00.000

(4 行受影响)
*/

[Dear SQL(燊)]

--處理時加一個輔助字段ID
declare @Tab table
(ID INT ,Num int, Name varchar(2),   Time DATETIME)
insert into @tab select 1, 1    ,'a',        '2009/05/01'
insert into @tab select 2, 1    ,'a',        '2009/05/02'
insert into @tab select 3, 1    ,'a',        '2009/05/03'
insert into @tab select 4,2    ,'b',        '2009/05/04'
insert into @tab select 5,2    ,'b',        '2009/05/05'
insert into @tab select 6,3    ,'c',        '2009/05/06'
insert into @tab select 7,3    ,'c',        '2009/05/07'
insert into @tab select 8,5    ,'e',        '2009/05/08'
insert into @tab select 9,1    ,'a',        '2009/05/09'
insert into @tab select 10,1    ,'a',        '2009/05/10' 

select  * from @Tab t  where name<>isnull((select top 1 isnull(name,'') from @tab where id=t.id-1 order by id ),'') 

[yanleiyigan]

select * from(select row_number() over(partition by mailrwid order by mailrwid) gid,* from pkeircltm0) tmp where gid=1

[abcdef1111111]

先收藏了,学习

[yanleiyigan]

select * from(select row_number() over(partition by num order by num) gid,* from tb) tmp where gid=1

[mybelta2]

值得学习

[百年树人]

select
  *
from 
  demo t
where
  not exists(select 1 from demo where num=t.num and [time]<t.[time])

[newtofy]

用得着那么复杂吗
很简单呀

select distinct Num ,(select max(Time) from Demowhere Demo.Num=dd.Num)
from Demo　dd


原理是　distinct取组，子查询取最大日期
关键在与　主查询必须　表名必须重命名

[newtofy]

落了个空格
更改如下

select distinct Num ,(select max(Time) from Demo where Demo.Num=dd.Num)
from Demo　dd

[bennyyyyy]

create table #test
(
Num int,
Name nvarchar(30),
TestTime datetime
)
insert into #test
select 1 , 'a', '2009/05/01' union all
select 1 , 'a', '2009/05/02' union all
select 1 , 'a', '2009/05/03' union all
select 2 , 'b', '2009/05/04' union all
select 2 , 'b', '2009/05/05' union all
select 3 , 'c', '2009/05/06' union all
select 3 , 'c', '2009/05/07' union all
select 5 , 'e', '2009/05/08' union all
select 1 , 'a', '2009/05/09' union all
select 1 , 'a', '2009/05/10'

select Num,Name,TestTime from(
select Num,Name,TestTime,ROW_NUMBER()over(partition by Num order by TestTime) Rank1 from #test
) A where A.Rank1=1

[SQL77]

SELECT DISTINCT T.*  FROM TB T,
(SELECT NUM,NAME,MIN(TIME)TIME FROM TB GROUP BY NUM,NAME) T1
WHERE T.NUM=T1.NUM
AND  T.NAME=T1.NAME

[lfywy]

[Quote=引用 12 楼 newtofy 的回复:]
落了个空格
更改如下

select distinct Num ,(select max(Time) from Demo where Demo.Num=dd.Num)
from Demo　dd
[/Quote]

鉴定：本人利用这个做出来了，很好，谢谢了！

[老黎]

select *
from demo a
where [time] = (select min([time] from demo where Num = a.Num and [Name] = a.[Name]))

[老黎]

[Quote=引用 16 楼 kerafan 的回复:]
SQL code

select *
from demo a
where [time] = (select min([time] from demo where Num = a.Num and [Name] = a.[Name]))
[/Quote]

--括号错位
select *
from demo a
where [time] = (select min([time]) from demo where Num = a.Num and [Name] = a.[Name])

[喜-喜]

use test
go

declare @Tab table
(Num int, Name varchar(2), Time DATETIME)
insert into @tab select 1 ,'a', '2009/05/01'
insert into @tab select 1 ,'a', '2009/05/02'
insert into @tab select 1 ,'a', '2009/05/03'
insert into @tab select 2 ,'b', '2009/05/04'
insert into @tab select 2 ,'b', '2009/05/05'
insert into @tab select 3 ,'c', '2009/05/06'
insert into @tab select 3 ,'c', '2009/05/07'
insert into @tab select 5 ,'e', '2009/05/08'
insert into @tab select 1 ,'a', '2009/05/09'
insert into @tab select 1 ,'a', '2009/05/10'

select * from @Tab where Time in (select min(Time) from @Tab group by (Name))

/*1
Num Name Time
-------------------------------
1 a 2009-05-01 00:00:00.000
2 b 2009-05-04 00:00:00.000
3 c 2009-05-06 00:00:00.000
5 e 2009-05-08 00:00:00.000
*/

[火星大能猫]

[Quote=引用 14 楼 sql77 的回复:]
SQL code

SELECT DISTINCT T.* FROM TB T,
(SELECT NUM,NAME,MIN(TIME)TIME FROM TB GROUP BY NUM,NAME) T1
WHERE T.NUM=T1.NUM
AND T.NAME=T1.NAME
[/Quote]
很好.

[yujiayou]

写的好复杂哦，你要去几条就在select后面叫Top 10表示取前面10条

[yjwcwrkks]

学习...

[蓝色-石头]

学习。不过有几个语句看不懂。。。