求分组sql

2013-12-03 11:26:18 301 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

无人机避障——卡内基梅隆大学（CMU）CERLAB 无人机自主框架复现 CMUCERLAB无人机自主框架是一个模块化的无人机自主导航系统，包含感知、测绘、规划和控制等组件。安装过程中遇到的主要问题包括：(1)cv_bridge路径未正确识别，需手动指定路径；(2)OpenCV版本冲突（4.2与4.6），导致动态导航节点崩溃。解决方案包括：完全卸载OpenCV4.2，重新编译安装OpenCV4.6，并修改相关CMakeLists文件指定版本。最终成功运行仿真环境，实现无人机在Gazebo和RViz中的自主避障导航功能。阅读详情



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

...全文

水文预报新手别怕！用Matlab 18小时搞定马斯京根法参数K和x（附完整代码）本文详细介绍了如何使用Matlab在18小时内快速掌握水文预报中的马斯京根法参数K和x的求解方法。通过完整的代码示例和分步解析，帮助新手从理论到实践快速跨越，解决传统手工试算耗时耗力的问题，提升水文预报的效率和精度。阅读详情

jianglai11

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

ChinaITOldMan 2013-12-05

打赏
举报

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

t101lian 2013-12-04

打赏
举报

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

jianglai11 2013-12-03

打赏
举报

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

Landa_Jimmy 2013-12-03

打赏
举报



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 行受影响)

昵称90天可改一次 2013-12-03

打赏
举报


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
*/

xxfvba 2013-12-03

打赏
举报

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

专注or全面 2013-12-03

打赏
举报


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

--以上没看懂啥意思？


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

LongRui888 2013-12-03

打赏
举报

引用 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
*/

求分组sql

相关推荐