34,597
社区成员
发帖
与我相关
我的任务
分享
有如下一些字段
id name time
1 AAA 2011-4-25 8:43:04
2 AAA 2011-4-25 9:15:09
3 BBB 2011-4-25 9:43:10
4 AAA 2011-4-25 9:43:01
5 BBB 2011-4-25 9:49:22
6 CCC 2011-4-25 9:50:24
7 AAA 2011-4-25 11:50:44
我想取出如下数据(规则,在一个小时内出现的数据只统计一次)
name time
AAA 2011-4-25 8:43:04
BBB 2011-4-25 9:43:10
CCC 2011-4-25 9:50:24
AAA 2011-4-25 11:50:44
各位帮帮忙!
select *
from tb t
where not exists (select 1 from tb where [name] = t.[name] --姓名相同
and convert(varchar(13),time,120) = convert(varchar(13),t.time,120) --时相等
and time < t.time --取时间较小的!
)
SELECT a.*
FROM tb a
INNER JOIN
(
SELECT [name],date=CONVERT(CHAR(13), GETDATE(), 120), id=MIN(id)
FROM tb
GROUP BY [name],CONVERT(CHAR(13), GETDATE(), 120)
) b
ON a.id = b.id
SELECT a.*
FROM tb a
INNER JOIN
(
SELECT date=CONVERT(CHAR(13), GETDATE(), 120), id=MIN(id)
FROM tb
GROUP BY CONVERT(CHAR(13), GETDATE(), 120)
) b
ON a.id = b.id
SELECT a.*
FROM tb a
INNER JOIN
(
SELECT [name], date=CONVERT(CHAR(10), [time], 120), hour=DATEPART(HOUR, [time]), id=MIN(id)
FROM tb
GROUP BY [name], CONVERT(CHAR(10), [time], 120),DATEPART(HOUR, [time])
) b
ON a.id = b.id
select *
from tb t
where time = (select min(time) from tb where [name] = t.[name]
and convert(varchar(13,[time],120))=convert(varchar(13,t.[time],120)))
select *
from tb t
where not exists (select 1 from tb where [name] = t.[name]
and convert(varchar(13,[time],120))=convert(varchar(13,t.[time],120)) and time < t.time)
select *
from tb t
where not exists (select 1 from tb where [name] = t.[name] and convert(varchar(16),time,120) < convert(varchar(16),t.time,120))
select *
from tb t
where time = (select min(time) from tb where [name] = t.[name])
select *
from tb t
where not exists (select 1 from tb where [name] = t.[name] and time < t.time)