请教下SQL查询语句,现有一张表table_text,数据量很大(上万条记录),字段和结构如下:
table_text
LOGDATE ID LOGTIME S_ID ACTNAME
20090703 9ec1f8606ac411 11:05:57 1112 .......
20090703 2j24223hkjh244 11:09:24 1112 .......
20090703 6y28jb86k3n9v1 16:05:10 1999 .......
20090620 3e42hi52342h22 09:24:22 1454 .......
20090531 5e6678i2u65524 17:43:08 1231 .......
20090812 6y7ui89kj5h35j 14:52:29 1672 .......
20090812 1jk5id8w4c9q00 08:55:20 1231 .......
20090503 7jt5r9x4z2m86r 15:22:11 1390 .......
........ .............. ........ ..... .......
其中 S_ID 近100个,每天不同时间都会有新的数据加入;
现在想查出 table_text 中,每个 S_ID 最早的 LOGDATE,并且以 S_ID 的顺序列出来
(如果查询结果中 S_ID 对应的 LOGDATE 有多个相同的日期,只显示一条记录)我上网找了些类似的查询语句修改了一下:
select LOGDATE,S_ID from table_text where S_ID in (select distinct S_ID from table_text) order by S_ID
可是貌似查询速度慢,而且 LOGDATE 不是唯一的...........
请各位老师教一下!!!谢谢!!!
table_text
LOGDATE ID LOGTIME S_ID ACTNAME
20090703 9ec1f8606ac411 11:05:57 1112 .......
20090703 2j24223hkjh244 11:09:24 1112 .......
20090703 6y28jb86k3n9v1 16:05:10 1999 .......
20090620 3e42hi52342h22 09:24:22 1454 .......
20090531 5e6678i2u65524 17:43:08 1231 .......
20090812 6y7ui89kj5h35j 14:52:29 1672 .......
20090812 1jk5id8w4c9q00 08:55:20 1231 .......
20090503 7jt5r9x4z2m86r 15:22:11 1390 .......
........ .............. ........ ..... .......
其中 S_ID 近100个,每天不同时间都会有新的数据加入;
现在想查出 table_text 中,每个 S_ID 最早的 LOGDATE,并且以 S_ID 的顺序列出来
(如果查询结果中 S_ID 对应的 LOGDATE 有多个相同的日期,只显示一条记录)我上网找了些类似的查询语句修改了一下:
select LOGDATE,S_ID from table_text where S_ID in (select distinct S_ID from table_text) order by S_ID
可是貌似查询速度慢,而且 LOGDATE 不是唯一的...........
请各位老师教一下!!!谢谢!!!
(select t.*,
row_number()over(partition by s_id order by logdate,logtime)rn
from table_text t)
where rn=1
(select t.*,
row_number()over(partition by s_id order by logdate,logtime)rn
from table_text t)
where rn=1
order by s_id
from
(
select LOGDATE,ID,LOGTIME,S_ID,ACTNAME,row_number() over(partition by S_ID order by LOGDATE) rn
from table_text
)
where rn = 1;
(
select a.*,(row_number() over(partition by s_id,logdate order by s_id)) as num from table_text a
) a
where a.num=1
(
select a.*,(row_number() over(partition by s_id,logdate order by s_id)) as num from table_text a
) a
where a.num=1
order by s_id
select LOGDATE,ID,LOGTIME,S_ID,ACTNAME
from
(
select LOGDATE,ID,LOGTIME,S_ID,ACTNAME,row_number() over(partition by S_ID order by LOGDATE) rn
from table_text
)
where rn = 1
order by S_ID;