数据列判断每天的最大值,怎么做
表中列:ID、DateTime、Date、XXX、Mark
DataTime列 就是 DateTime时间格式
Date列 为当天日期的8位数字字符串(如:20130925)
XXX 为每天从第一条数据到最后一条数据的序号,从1开始
ID = Date+XXX(如:20130925001、20130925002、20130925003)
问题:用什么语句给每天最大XXX这行数据标记 Mark 值为 1
知道有个这:max(XXX) over (partition by Date)
但是不知道怎么用,还有什么方法?
[解决办法]
select ID,DateTime,Date,XXX,1 as 'Mark'
from table a
where exists (select 1 from (
select max(xxx)xxx,datetime
from table
group by datetime)b where a.xxx=b.xxx and a.datetime=b.datetime)
[解决办法]
--方法一
;with Tmp as (
select Date,max(ID) as MaxID from [Table] group by Date
)
update [Table] set Mark = 1
from [Table] A inner join Tmp B ON A.Date = B.Date AND A.id = B.MaxID
--方法二
;with Tmp as (
select row_number() over (partition by Date order by XXX desc ) as Row Date,ID from [Table]
)
update [Table] set Mark = 1
from [Table] A inner join Tmp B ON A.Date = B.Date AND A.id = B.MaxID and b.Row = 1
--最简单的
select right(convert(varchar(16),max(ID)),len(convert(varchar(16),max(ID))) -8) as MaxID
from [Table] group by Date
update t1 set t1.Mark=1
from tbl t1
where exists (select 1 from (
select max(xxx)xxx,convert(varchar(10),datetime,120) datetime
from tbl
group by convert(varchar(10),datetime,120)) t2 where t1.xxx=t2.xxx and convert(varchar(10),t1.datetime,120)=convert(varchar(10),t2.datetime,120))