帮忙找下错。

2010-07-09 08:32:37 248 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

（MATLAB）一维信号峰值检测findpeaks 一维信号波峰波谷检测，峰值、峰宽、到达时间计算。MATLAB自带的findpeaks函数能完成这一任务，在这里分享其基本用法与特殊用法。阅读详情



declare @begin datetime

declare @end  datetime	--结束时间

declare @stcd char(8)



set @begin='2010-5-20 15:15:54'

set @end='2010-7-20 15:16:13'

set @stcd='90800001'



--AS

--begin

select a.stcd,b.h1yp,convert(varchar(10),a.TM,20) datadate

from st_rain_s a inner join

(select stcd,max(p8) h1yp,convert(varchar(10),TM,20) dt1 from

(

select stcd,p8,convert(varchar(10),TM,20) dt1 from st_rain_s 

union all select stcd,p9,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p10,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p11,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p12,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p13,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p14,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p15,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p16,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p17,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p18,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p19,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p20,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p21,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p22,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p23,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p0,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p1,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p2,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p3,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p4,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p5,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p6,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p7,convert(varchar(10),TM,20) dt1 from st_rain_s

) tb1 group by tb1.stcd

) b on b.stcd=a.stcd where a.stcd=@stcd and TM between @begin and @end

group by a.stcd,b.h1yp,convert(varchar(10),a.TM,20)



GO

提示：列名‘TM’无效。

...全文

03.Linux下目录动态扩容，以分配home的部分空间给root为例本篇文章将介绍Linux下分配home目录的部分空间给root目录。大致思路：卸载home，解除其磁盘容量占用->扩容root，增加其磁盘空间->重建恢复home操作演示环境为CentOS系统，其他操作系统例如openEuler也可作为参考。01.Linux下目录扩容，以增加root目录的磁盘容量分配为例操作系统版本如下名称版本CentOS7.6+ 阅读详情

net_xiaojian

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

zklovehh 2010-07-10

打赏
举报

顶0000000000

net_xiaojian 2010-07-09

打赏
举报

恩，是2000

net_xiaojian 2010-07-09

打赏
举报

谢谢各位了。

SQL77 2010-07-09

打赏
举报

[Quote=引用 19 楼 net_xiaojian 的回复:]
怎么执行，;WITH t AS 这样的提示错误。
[/Quote]
你是2000吧,

htl258_Tony 2010-07-09

打赏
举报

--> 生成测试数据表: [st_rain_s]

IF OBJECT_ID('[st_rain_s]') IS NOT NULL

	DROP TABLE [st_rain_s]

GO

create table st_rain_s

(

stcd char(8)

,TM datetime

,DYP float

,p8 float

,p9 float

,p10 float

,p11 float

)



insert st_rain_s

select '90800001','2010-7-8 21:02:58',10,1,2,3,4

union all

select '90800001','2010-7-9 21:02:58',20,10,3,3,4





-->SQL查询如下:

DECLARE @stcd   VARCHAR(8)

DECLARE @begin  DATETIME

DECLARE @end    DATETIME



SET @stcd = '90800001'

SET @begin = '2010-7-7 20:07:44'

SET @end = '2010-7-9 21:07:52'



SELECT stcd, tm, (

           SELECT MAX(p) 

           FROM (

                    SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11

                ) a

       ) AS maxp 

FROM (

    SELECT stcd, CONVERT(VARCHAR(10), TM, 20) tm, MAX(p8) p8, MAX(p9) p9, MAX(p10) p10, MAX(p11) p11

    FROM st_rain_s

    WHERE stcd=@stcd AND TM BETWEEN @begin AND @end

    GROUP BY stcd, CONVERT(VARCHAR(10), TM, 20)

) as t 

/*

stcd     tm         maxp

-------- ---------- ----------------------

90800001 2010-07-08 4

90800001 2010-07-09 10



(2 行受影响)

*/

这样就不错了。我在2008下执行的。

SQL77 2010-07-09

打赏
举报

DECLARE   @st_rain_s table

(

stcd char(8)

,TM datetime

,DYP float

,p8 float

,p9 float

,p10 float

,p11 float

)



select * from @st_rain_s



insert @st_rain_s

select '90800001','2010-7-8 21:02:58',10,1,2,3,4

union all

select '90800001','2010-7-9 21:02:58',20,10,3,3,4



--测试

declare @stcd char(8)

declare @begin datetime

declare @end datetime



set @stcd='90800001'

set @begin='2010-7-7 20:07:44'

set @end='2010-7-9 21:07:52'



SELECT stcd,TM,MAX(P8) FROM 

(



SELECT stcd,TM,P8 FROM @st_rain_s

UNION ALL

SELECT stcd,TM,P9 FROM @st_rain_s

UNION ALL

SELECT stcd,TM,P10 FROM @st_rain_s

UNION ALL

SELECT stcd,TM,P11 FROM @st_rain_s

)T

GROUP BY STCD,TM



/*stcd     TM                                                     DYP                                                   p8                                                    p9                                                    p10                                                   p11                                                   

-------- ------------------------------------------------------ ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- 



（所影响的行数为 0 行）





（所影响的行数为 2 行）



stcd     TM                                                                                                           

-------- ------------------------------------------------------ ----------------------------------------------------- 

90800001 2010-07-08 21:02:58.000                                4.0

90800001 2010-07-09 21:02:58.000                                10.0



（所影响的行数为 2 行）

net_xiaojian 2010-07-09

打赏
举报

怎么执行，;WITH t AS 这样的提示错误。

htl258_Tony 2010-07-09

打赏
举报

--> 生成测试数据表: [st_rain_s]

IF OBJECT_ID('[st_rain_s]') IS NOT NULL

	DROP TABLE [st_rain_s]

GO

create table st_rain_s

(

stcd char(8)

,TM datetime

,DYP float

,p8 float

,p9 float

,p10 float

,p11 float

)



insert st_rain_s

select '90800001','2010-7-8 21:02:58',10,1,2,3,4

union all

select '90800001','2010-7-9 21:02:58',20,10,3,3,4





-->SQL查询如下:

DECLARE @stcd   VARCHAR(8)

DECLARE @begin  DATETIME

DECLARE @end    DATETIME



SET @stcd = '90800001'

SET @begin = '2010-7-7 20:07:44'

SET @end = '2010-7-9 21:07:52'



;WITH t AS

(

    SELECT stcd, CONVERT(VARCHAR(10), TM, 20) tm, MAX(p8) p8, MAX(p9) p9, MAX(p10) p10, MAX(p11) p11

    FROM st_rain_s

    WHERE stcd=@stcd AND TM BETWEEN @begin AND @end

    GROUP BY stcd, CONVERT(VARCHAR(10), TM, 20)

)

SELECT stcd, tm, (

           SELECT MAX(p) 

           FROM (

                    SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11

                ) a

       ) AS maxp 

FROM t

/*

stcd     tm         maxp

-------- ---------- ----------------------

90800001 2010-07-08 4

90800001 2010-07-09 10



(2 行受影响)

*/

net_xiaojian 2010-07-09

打赏
举报



insert st_rain_s

select '90800001','2010-7-8 21:02:58',10,1,2,3,4

union all

select '90800001','2010-7-9 21:02:58',20,10,3,3,4

p8,p9,p10,p11,单个最大值 group by stcd,TM

SQL77 2010-07-09

打赏
举报

[Quote=引用 15 楼 htl258 的回复:]
前面的4和10怎么得到的？
[/Quote]
UP TONY 哥的耐心

htl258_Tony 2010-07-09

打赏
举报

前面的4和10怎么得到的？

net_xiaojian 2010-07-09

打赏
举报

写了一个简单的测试用例，还是有问题。



create database maxColumn

use maxColumn



create table st_rain_s

(

stcd char(8)

,TM datetime

,DYP float

,p8 float

,p9 float

,p10 float

,p11 float

)



select * from st_rain_s



insert st_rain_s

select '90800001','2010-7-8 21:02:58',10,1,2,3,4

union all

select '90800001','2010-7-9 21:02:58',20,10,3,3,4



--测试

declare @stcd char(8)

declare @begin datetime

declare @end datetime



set @stcd='90800001'

set @begin='2010-7-7 20:07:44'

set @end='2010-7-9 21:07:52'



select a.stcd,b.h1yp,convert(varchar(10),a.TM,20) datadate

from st_rain_s a inner join

(select stcd,max(p8) h1yp,convert(varchar(10), dt1 ,20) dt1 from

(

select stcd,p8,convert(varchar(10),TM,20) dt1 from st_rain_s 

union all select stcd,p9,convert(varchar(10),TM,20) dt1 from st_rain_s union all select stcd,p10,convert(varchar(10),TM,20) dt1 from st_rain_s

union all select stcd,p11,convert(varchar(10),TM,20) dt1 from st_rain_s

) tb1 group by tb1.stcd,tb1.dt1

) b on b.stcd=a.stcd where a.stcd=@stcd and TM between @begin and @end

group by a.stcd,b.h1yp,convert(varchar(10),a.TM,20)



--数据显示如下：

4.0	2010-07-08	90800001

4.0	2010-07-09	90800001

10.0	2010-07-08	90800001

10.0	2010-07-09	90800001



--我需要下列数据

4.0	2010-07-08	90800001

10.0	2010-07-09	90800001

htl258_Tony 2010-07-09

打赏
举报

--> 生成测试数据表: [st_rain_s]

IF OBJECT_ID('[st_rain_s]') IS NOT NULL

	DROP TABLE [st_rain_s]

GO

CREATE TABLE [st_rain_s] ([stcd] [nvarchar](10),[tm] [datetime],[dyp] [nvarchar](10),[p8] [int],[p9] [int],[p10] [int],[p5] [int],[p6] [int],[p7] [int])

INSERT INTO [st_rain_s]

SELECT 'a','20100101','a','1','2','3','4','5','3' UNION ALL

SELECT 'a','20100101','a','1','2','3','6','5','3' UNION ALL

SELECT 'b','20100103','b','11','22','3','4','5','3'



--SELECT * FROM [st_rain_s]



-->SQL查询如下:

;WITH t AS

(

    SELECT stcd, CONVERT(VARCHAR(10), TM, 20) tm, MAX(p8) p8, MAX(p9) p9, MAX(p10) p10, MAX(p5) p5, 

           MAX(p6) p6, MAX(p7) p7 

    FROM st_rain_s

    GROUP BY stcd, CONVERT(VARCHAR(10), TM, 20)

)

SELECT stcd, tm, (

           SELECT MAX(p) 

           FROM (

                    SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p5 UNION SELECT p6 

                    UNION SELECT p7

                ) a

       ) AS maxp 

FROM t

/*

stcd       tm         maxp

---------- ---------- -----------

a          2010-01-01 6

b          2010-01-03 22



(2 行受影响)

*/

htl258_Tony 2010-07-09

打赏
举报

;with t as

(

  select stcd,convert(varchar(10),a.TM,20) tm,max(p8) p8,max(p9) p9,max(p10) p10,max(p5) p5,max(p6) p6,max(p7) p7

  from st_rain_s

  group by stcd,convert(varchar(10),a.TM,20)

)

select stcd,tm,(select max(p) from (select p=p8 union select p9 union select p10 union select p5 union select p6 union select p7) a) as maxp

from t

net_xiaojian 2010-07-09

打赏
举报



create table st_rain_s

(

stcd char(8)

,TM datetime

,DYP float

,p8 float

.

.--这里字段为 p9,p10,....,p5,p6

.

,p7 float

)

就是统计 group by stcd,TM,max(1列或多列数据之和求最大值)

htl258_Tony 2010-07-09

打赏
举报

可否贴点测试数据，或许代码不需要那么多。

net_xiaojian 2010-07-09

打赏
举报

4楼改一下和6楼一样
得到的数据都为：

h1yp datadate stcd

32.0 2010-07-08 90800001
32.0 2010-07-09 90800001
90.0 2010-07-08 90800001
90.0 2010-07-09 90800001

我要的数据应该是
h1yp datadate stcd

32.0 2010-07-09 90800001
90.0 2010-07-08 90800001

SQL77 2010-07-09

打赏
举报

select a.stcd,b.h1yp,convert(varchar(10),a.TM,20) datadate
from st_rain_s a inner join
(
select stcd,max(p8) h1yp,convert(varchar(10),TM,20)--没必要了吧,你子查询已经有别名为 dt1 from
(
select stcd,p8,convert(varchar(10),TM,20) TM from st_rain_s
union all select stcd,p9,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p10,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p11,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p12,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p13,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p14,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p15,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p16,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p17,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p18,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p19,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p20,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p21,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p22,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p23,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p0,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p1,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p2,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p3,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p4,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p5,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p6,convert(varchar(10),TM,20) dt1 from st_rain_s
union all select stcd,p7,convert(varchar(10),TM,20) dt1 from st_rain_s
) tb1
group by stcd,convert(varchar(10),TM,20)
) b
on b.stcd=a.stcd
where a.stcd=@stcd and a.DT1 between @begin and @end
group by a.stcd,b.h1yp,convert(varchar(10),a.TM,20)
GO

SQL77 2010-07-09

打赏
举报

select stcd,max(p8) h1yp,convert(varchar(10),TM,20) dt1 from
(
select stcd,p8,convert(varchar(10),TM,20) dt1 from st_rain_s

你子查询已经有别名请使用别名

htl258_Tony 2010-07-09

打赏
举报

declare @begin datetime

declare @end  datetime    --结束时间

declare @stcd char(8)



set @begin='2010-5-20 15:15:54'

set @end='2010-7-20 15:16:13'

set @stcd='90800001'



--AS

--begin

select a.stcd,b.h1yp,convert(varchar(10),a.TM,20) datadate

from st_rain_s a inner join

(

	select stcd,max(p8) h1yp,TM from

	(

		select stcd,p8,convert(varchar(10),TM,20) TM from st_rain_s 

		union all select stcd,p9,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p10,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p11,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p12,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p13,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p14,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p15,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p16,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p17,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p18,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p19,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p20,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p21,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p22,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p23,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p0,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p1,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p2,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p3,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p4,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p5,convert(varchar(10),TM,20) dt1 from st_rain_s 

		union all select stcd,p6,convert(varchar(10),TM,20) dt1 from st_rain_s

		union all select stcd,p7,convert(varchar(10),TM,20) dt1 from st_rain_s

	) tb1 

	group by stcd,TM

) b 

	on b.stcd=a.stcd AND convert(varchar(10),a.TM,20)=b.TM

where a.stcd=@stcd and a.TM between @begin and @end

group by a.stcd,b.h1yp,convert(varchar(10),a.TM,20)

GO

语法这个应该是没问题的

加载更多回复（5）

帮忙找下错。

相关推荐