求分组sql

[jianglai11]

create table tb(price DECIMAL(18,2), RecordDate DATETIME)
  
--临时数据
insert into tb 
select 1.23000,'2013-04-24 00:00:00'
union all select  1.25000,	'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-26 00:00:00'
union all select  1.28000	,'2013-04-26 00:00:00'
union all select  1.25000	,'2013-04-27 00:00:00'
union all select  1.25000	,'2013-05-06 00:00:00'
union all select  1.24000	,'2013-05-06 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.30000	,'2013-10-26 00:00:00'
union all select  1.31000	,'2013-10-26 00:00:00'
 
  SELECT * FROM tb
 --求价格变化趋势Sql,并记录变化的日期，
 --如果同一日期则记录最大值，
 --记录变化日期的最早日期，
 --如4-27和5-6同价则记录4-27
 --期望结果如下：
--1.23	2013-04-24 00:00:00.000
--1.25	2013-04-25 00:00:00.000  
--1.28	2013-04-26 00:00:00.000
--1.25	2013-04-27 00:00:00.000
--1.24	2013-05-06 00:00:00.000
--1.31	2013-10-26 00:00:00.000

[LongRui888]

引用 2 楼 jianglai11 的回复:

--1.25 2013-04-27 00:00:00.000 楼上的缺少这条记录

试试这个：

;with t
as
(
select *,
       ROW_NUMBER() over(order by recorddate) rownum,
       ROW_NUMBER() over(PARTITION by price order by recorddate) as rn
from tb
)

select price,recorddate
from
(
select *,
       row_number() over(partition by price,rownum - rn order by rownum) rownum2
from t
)t
where rownum2 = 1
order by recorddate
/*
price	recorddate
1.23	2013-04-24 00:00:00.000
1.25	2013-04-25 00:00:00.000
1.28	2013-04-26 00:00:00.000
1.25	2013-04-27 00:00:00.000
1.24	2013-05-06 00:00:00.000
1.30	2013-10-26 00:00:00.000
1.31	2013-10-26 00:00:00.000
*/

[专注or全面]

--如果同一日期则记录最大值，
 --记录变化日期的最早日期，
 --如4-27和5-6同价则记录4-27

--以上没看懂啥意思？


select MAX(price),RecordDate from tb
group by RecordDate
order by RecordDate

[xxfvba]

with cte as (select *,rn=row_number() over (order by getdate()) from tb ) select a.price,a.recorddate from cte a,cte b where a.rn=b.rn+1 and a.price<>b.price union select price,recorddate from cte where rn=1 order by recorddate

[昵称90天可改一次]

create table test(id int identity(1,1) not null,price DECIMAL(18,2), RecordDate DATETIME)

insert test(price,RecordDate)
SELECT price,RecordDate from tb

select price,RecordDate
FROM test t
WHERE not EXISTS (
  SELECT *
  FROM test 
  WHERE price = t.price AND id=t.id-1
)

/*
1.23	2013-04-24 00:00:00.000
1.25	2013-04-25 00:00:00.000
1.28	2013-04-26 00:00:00.000
1.25	2013-04-27 00:00:00.000
1.24	2013-05-06 00:00:00.000
1.30	2013-10-26 00:00:00.000
1.31	2013-10-26 00:00:00.000
*/

[Landa_Jimmy]

create table #tb(price DECIMAL(18,2), RecordDate DATETIME)
  
--临时数据
insert into #tb 
select 1.23000,'2013-04-24 00:00:00'
union all select  1.25000,	'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-25 00:00:00'
union all select  1.25000	,'2013-04-26 00:00:00'
union all select  1.28000	,'2013-04-26 00:00:00'
union all select  1.25000	,'2013-04-27 00:00:00'
union all select  1.25000	,'2013-05-06 00:00:00'
union all select  1.24000	,'2013-05-06 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.24000	,'2013-05-12 00:00:00'
union all select  1.30000	,'2013-10-26 00:00:00'
union all select  1.31000	,'2013-10-26 00:00:00'

  select price,RecordDate from (
  SELECT ROW_NUMBER()over(partition by price order by RecordDate)id,* FROM #tb
  )a where id=1
price                                   RecordDate
--------------------------------------- -----------------------
1.23                                    2013-04-24 00:00:00.000
1.24                                    2013-05-06 00:00:00.000
1.25                                    2013-04-25 00:00:00.000
1.28                                    2013-04-26 00:00:00.000
1.30                                    2013-10-26 00:00:00.000
1.31                                    2013-10-26 00:00:00.000

(6 行受影响)

[jianglai11]

--1.25 2013-04-27 00:00:00.000 楼上的缺少这条记录

[t101lian]

create table #tb(price DECIMAL(18,2), RecordDate DATETIME)
   
--临时数据
insert into #tb 
select 1.23000,'2013-04-24 00:00:00'
union all select  1.25000,    '2013-04-25 00:00:00'
union all select  1.25000    ,'2013-04-25 00:00:00'
union all select  1.25000    ,'2013-04-25 00:00:00'
union all select  1.25000    ,'2013-04-26 00:00:00'
union all select  1.28000    ,'2013-04-26 00:00:00'
union all select  1.25000    ,'2013-04-27 00:00:00'
union all select  1.25000    ,'2013-05-06 00:00:00'
union all select  1.24000    ,'2013-05-06 00:00:00'
union all select  1.24000    ,'2013-05-12 00:00:00'
union all select  1.24000    ,'2013-05-12 00:00:00'
union all select  1.24000    ,'2013-05-12 00:00:00'
union all select  1.30000    ,'2013-10-26 00:00:00'
union all select  1.31000    ,'2013-10-26 00:00:00'
  
  SELECT * FROM #tb

select MAX(price),RecordDate from #tb
group by RecordDate
order by RecordDate
price                RecordDate
---------------------------------
1.23	2013-04-24 00:00:00.000
1.25	2013-04-25 00:00:00.000
1.28	2013-04-26 00:00:00.000
1.25	2013-04-27 00:00:00.000
1.25	2013-05-06 00:00:00.000
1.24	2013-05-12 00:00:00.000
1.31	2013-10-26 00:00:00.000

[ChinaITOldMan]

select MAX(price),RecordDate from #tb group by RecordDate order by RecordDate