数据库查询,
有一表data
dataid userid value date
1 1 123 2005-10-01 10:20:00
2 1 124 2005-10-01 10:25:00
3 1 145 2005-10-01 10:30:00
. ... ......
789 1 789 2008-12-2 10:25:00
数据记录每5分钟一条
要查询每个月的第一条记录,每个星期的第一条记录,sql语句要怎么写啊
有一表data
dataid userid value date
1 1 123 2005-10-01 10:20:00
2 1 124 2005-10-01 10:25:00
3 1 145 2005-10-01 10:30:00
. ... ......
789 1 789 2008-12-2 10:25:00
数据记录每5分钟一条
要查询每个月的第一条记录,每个星期的第一条记录,sql语句要怎么写啊
from [data] a
where not exists(select 1 from [data] where datediff(mm,a.[date],[date]) = 0 and [date]< a.[date])
select *
from [data] a
where not exists(select 1 from [data] where datediff(wk,a.[date],[date]) = 0 and [date]< a.[date])
select t.* from date t where date = (select min(date) from tb where convert(varchar(7),date,120) = convert(varchar(7),t.date,120))--每周
select t.* from date t where date = (select min(date) from tb where datepart(week,date) = datepart(week,t.date))
select t.* from date t where date = (select min(date) from tb where convert(varchar(7),date,120) = convert(varchar(7),t.date,120)) --每周 (应该还加个年的判断)
select t.* from date t where date = (select min(date) from tb where year(date) = year(t.date) and datepart(week,date) = datepart(week,t.date))
select * from date t where date = (select min(date) from tb where year(date)=year(t.date) and month(date)=month(t.date)) 每周
select * from date t where date = (select min(date) from tb where datepart(wk,date) = datepart(wk,t.date))
select * from date t where date = (select min(date) from tb where year(date)=year(t.date) and month(date)=month(t.date)) 每周
select * from date t where date = (select min(date) from tb where year(date) = year(t.date) and datepart(wk,date) = datepart(wk,t.date)) 也漏考虑了.
select * from data where datepart(dd,date) = 1and date = (select min(date) from data where datediff(date,a.date) = 0)
union
select * from data where datepart(dw,date) = 1and date = (select min(date) from data where datediff(date,a.date) = 0)
select * from data a where datepart(dd,date) = 1and date = (select min(date) from data where datediff(date,a.date) = 0)
union
select * from data a where datepart(dw,date) = 1and date = (select min(date) from data where datediff(date,a.date) = 0)