数据如下:
SELECT HistoryId,Score,CONVERT(VARCHAR(10),Add_Datetime,23)as 'Add_Date'
from Table Group by Add_Datetime
HistoryId Score Add_Date
=====================================
1 12 2013-01-02
2 43 2013-01-02
3 50 2013-01-02
4 23 2013-01-02
5 89 2013-01-03
6 77 2013-01-03
7 63 2013-01-03
8 32 2013-01-04
9 90 2013-01-04
10 88 2013-01-04
=====================================以上是模拟的,跟元素据也差不多了。。需要得到的结果HistoryId Score Add_Date
=====================================
3 50 2013-01-02
5 89 2013-01-03
9 90 2013-01-04
=====================================
GroupBy 每天 得到 Max(Score),Max(Score)的HistoryId也要得到。反正结果要同时包含三列都有。谢谢了。
sqlGroupBySelect
SELECT HistoryId,Score,CONVERT(VARCHAR(10),Add_Datetime,23)as 'Add_Date'
from Table Group by Add_Datetime
HistoryId Score Add_Date
=====================================
1 12 2013-01-02
2 43 2013-01-02
3 50 2013-01-02
4 23 2013-01-02
5 89 2013-01-03
6 77 2013-01-03
7 63 2013-01-03
8 32 2013-01-04
9 90 2013-01-04
10 88 2013-01-04
=====================================以上是模拟的,跟元素据也差不多了。。需要得到的结果HistoryId Score Add_Date
=====================================
3 50 2013-01-02
5 89 2013-01-03
9 90 2013-01-04
=====================================
GroupBy 每天 得到 Max(Score),Max(Score)的HistoryId也要得到。反正结果要同时包含三列都有。谢谢了。
sqlGroupBySelect
(select max(score) from tb where t.Add_Date=Add_Date group by Add_Date)
go
create table [TB] (HistoryId int,Score int,Add_Date datetime)
insert into [TB]
select 1,12,'2013-01-02' union all
select 2,43,'2013-01-02' union all
select 3,50,'2013-01-02' union all
select 4,23,'2013-01-02' union all
select 5,89,'2013-01-03' union all
select 6,77,'2013-01-03' union all
select 7,63,'2013-01-03' union all
select 8,32,'2013-01-04' union all
select 9,90,'2013-01-04' union all
select 10,88,'2013-01-04'select * from [TB]--方法1
SELECT *
FROM dbo.TB B
WHERE NOT EXISTS(SELECT 1 FROM TB A WHERE A.add_date = B.add_date AND A.score>B.score)/*
HistoryId Score Add_Date
3 50 2013-01-02 00:00:00.000
5 89 2013-01-03 00:00:00.000
9 90 2013-01-04 00:00:00.000*/
score ,
add_date
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY add_date ORDER BY score DESC ) AS NO ,
*
FROM dbo.TB
) A
WHERE no = 1
只能这样写么???这样写我也想到了。
可是元数据已经连接查询了一次,Group By 2次了。。
这样写执行起来会很慢的数据不多也有好几万条啊!!!!能有简单点的么??
rowNumber 可能有点不行,因为在查询前会跟所有数据分配一个编号会慢。