帮忙找下错。

net_xiaojian 2010-07-09 08:32:37 


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’无效。
...全文
164 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
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）
如何开发框架(Framework)：以ListView为例
Android从View衍生出一个子类：ListView，来协助App开发者，让其轻易地开发出UI画面上的ListBox来。然而，仅仅一个ListView子类，并无法实现一个漂亮的ListBox画面。于是，规划一个小框架来达成这个目标，对App开发者而言，这个小框架可以帮忙很多。App开发者可以从这个小框架衍生出子类，就能轻易开发出漂亮的ListBox了。本课程就来详细明ListView框架。
AI 帮忙找 Bug，英特尔开源代码编程工具ControlFlag
英特尔的自动代码调试工具 ControlFlag 现在是开源的，可供开发人员免费访问——这一举措将使许多厌倦了花费数小时仔细检查软件程序以寻找潜在异常的人松了一口气。 ControlFlag 现在可通过 GitHub 获得，利用机器学习自动识别软件和固件代码中的错误，为开发人员节省手动调试他们编写的程序的耗时任务。 ControlFlag 于去年年底首次宣布，迄今为止仅由英特尔内部使用，以发现公司自身软件开发中的异常情况。通过向外部开发人员开放该工具并让他们在其上构建，英特尔希望突破系统所能做的极限，以简化
Android 解决DataBindingComponent包找不到的问题
前言 事情是这样的。。。。从github上，下载同事儿的新项目，同事用的DataBinding，但是报错了 错误就是DataBindingComponent 这个包名找不到 期间 同事也帮忙找错 但是都没有解决。 我是怎么解决的： 搞了一天半，这个问题就是没有解决 后来 我突发奇想 我创建一个同DataBindingComponent 一样路径的包名 类名 它会不会报DataBindingComponent 类已存在的错误呢？ 接下来 我就试了试！！！！！ 结果程序 真的报错了 说...
eclipse导入包找不到类的解决办法
@eclipse导入包找不到类的解决办法 在使用eclipse导入mysql-connector-java-5.0.8-bin.jar包之后，jdbc程序依然报错说：Exception in thread “main” java.lang.ClassNotFoundException: com.mysql.jdbc.Driver 在多次检查确认包没导错的情况下，我发现这是因为JDK16和mysql-connector-java-5.0.8之间的兼容性问题，虽然在idea上这两者是兼容的，但是eclipse上
程序员离职后，被前领导要求改代码，你会不会帮忙？
关注+星标公众号，不错过精彩内容来源| CSDN在职场上，如果一个员工离职了，那么与前公司应该没什么关系了。即使你在前公司所负责的工作出现问题，也与你无关，公司更没有权力要求你回去帮忙。...
疑难问题

22,207

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章