他那两种方法在sql2005里面没有问题,2000里面有问题 SELECT MAX(p) FROM ( SELECT p = p8 from st_rain_s UNION all SELECT p9 from st_rain_s UNION all SELECT p10 from st_rain_s UNION all SELECT p11 from st_rain_s ) a 可能是这里2000不认。1楼你的方法在我这里不行,@n int这个是多少列相加,我这里不合适,
--> 生成测试数据表: [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,4,2,3,4 union all select '90800001','2010-7-9 21:02:58',20,10,3,3,4 -->SQL查询如下: IF OBJECT_ID('p_test')>0 DROP PROC p_test GO CREATE PROC p_test @stcd VARCHAR(8), @begin VARCHAR(20), @end VARCHAR(20), @n INT --统计最多的列数 AS DECLARE @s VARCHAR(8000),@s1 VARCHAR(8000),@sql VARCHAR(8000) SELECT @s=ISNULL(@s+' UNION SELECT ','SELECT p=')+QUOTENAME(name), @s1=ISNULL(@s1+',','')+'MAX('+QUOTENAME(name)+')'+QUOTENAME(name) FROM syscolumns WHERE id=OBJECT_ID('st_rain_s') AND name NOT IN('stcd', 'TM','DYP') --筛选不参与统计的字段 DECLARE @i INT SET @i = 1 WHILE @i<=@n BEGIN SET @sql=ISNULL(@sql+',','')+'(SELECT SUM(p) FROM (SELECT TOP '+LTRIM(@i)+' p FROM ('+@s+')a ORDER BY 1 DESC) b) AS ['+LTRIM(@i)+'列最大值]' SET @i=@i+1 END EXEC( 'SELECT stcd, tm,'+@sql+ 'FROM ( SELECT stcd, CONVERT(VARCHAR(10), TM, 20) tm,'+@s1+' FROM st_rain_s WHERE stcd = '''+@stcd+''' AND TM BETWEEN '''+@begin+''' AND '''+@end+''' GROUP BY stcd, CONVERT(VARCHAR(10), TM, 20) ) AS t ') GOEXEC p_test '90800001','2010-7-7 20:07:44','2010-7-9 21:07:52',3 /* stcd tm 1列最大值 2列最大值 3列最大值 -------- ---------- ---------------------- ---------------------- ---------------------- 90800001 2010-07-08 4 7 9 90800001 2010-07-09 10 14 17(2 行受影响) */
--SQL2000 SELECT stcd, tm, ( SELECT MAX(p) FROM ( SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11 ) a ) AS 一列最大值, ( SELECT SUM(p) FROM ( SELECT TOP 2 p FROM ( SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11 ) a ORDER BY 1 DESC ) b ) AS 两列最大值, ( SELECT SUM(p) FROM ( SELECT TOP 3 p FROM ( SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11 ) a ORDER BY 1 DESC ) b ) AS 三列最大值 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 sql2005执行没问题,2000有问题。
xys_77 你的参数:@n INT --统计最多的列数如果n=24,是不是1--24列的和都显示出来,如果这样就不是我想要的。
--> 生成测试数据表: [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 DEC(18,2) ,p8 DEC(18,2) ,p9 DEC(18,2) ,p10 DEC(18,2) ,p11 DEC(18,2) ) insert st_rain_s select '90800001','2010-7-8 21:02:58',10,4,2,3,4 union all select '90800001','2010-7-9 21:02:58',20,10,3,3,4CREATE FUNCTION GET_MAXSUM(@stcd char(8),@TM datetime,@COLNUM INT=1) RETURNS INT AS BEGIN DECLARE @SUM INT SET @SUM= ( SELECT SUM(RN) FROM(
SELECT T.*,COUNT(T1.CNT)+1 AS COLNUM FROM (SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) t LEFT JOIN (SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) T1
ON T1.stcd=T.stcd and datediff(DD,T.tm,T1.tm)=0 and (T1.rn>t.rn or (T1.rn=t.rn and T1.cnt>T.cnt))
GROUP BY T.STCD,T.TM,T.RN,T.CNT ) T WHERE STCD=@STCD AND DATEDIFF(DD,TM,@TM)=0 AND COLNUM<=@COLNUM ) RETURN @SUM END SELECT STCD,TM,DYP,DBO.GET_MAXSUM(STCD,TM,1),DBO.GET_MAXSUM(STCD,TM,2),DBO.GET_MAXSUM(STCD,TM,3),DBO.GET_MAXSUM(STCD,TM,4) FROM st_rain_s/* STCD TM DYP -------- ------------------------------------------------------ -------------------- ----------- ----------- ----------- ----------- 90800001 2010-07-08 21:02:58.000 10.00 4 8 11 13 90800001 2010-07-09 21:02:58.000 20.00 10 14 17 20(所影响的行数为 2 行)*/ 写了个函数,楼主试试吧,没05的方便
FROM (
SELECT p = p8 from st_rain_s UNION all
SELECT p9 from st_rain_s UNION all SELECT p10 from st_rain_s UNION all SELECT p11 from st_rain_s
) a
可能是这里2000不认。1楼你的方法在我这里不行,@n int这个是多少列相加,我这里不合适,
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,4,2,3,4
union all
select '90800001','2010-7-9 21:02:58',20,10,3,3,4
-->SQL查询如下:
IF OBJECT_ID('p_test')>0
DROP PROC p_test
GO
CREATE PROC p_test
@stcd VARCHAR(8),
@begin VARCHAR(20),
@end VARCHAR(20),
@n INT --统计最多的列数
AS
DECLARE @s VARCHAR(8000),@s1 VARCHAR(8000),@sql VARCHAR(8000)
SELECT @s=ISNULL(@s+' UNION SELECT ','SELECT p=')+QUOTENAME(name),
@s1=ISNULL(@s1+',','')+'MAX('+QUOTENAME(name)+')'+QUOTENAME(name)
FROM syscolumns
WHERE id=OBJECT_ID('st_rain_s')
AND name NOT IN('stcd', 'TM','DYP') --筛选不参与统计的字段
DECLARE @i INT
SET @i = 1
WHILE @i<=@n
BEGIN
SET @sql=ISNULL(@sql+',','')+'(SELECT SUM(p) FROM (SELECT TOP '+LTRIM(@i)+' p FROM ('+@s+')a ORDER BY 1 DESC) b) AS ['+LTRIM(@i)+'列最大值]'
SET @i=@i+1
END
EXEC(
'SELECT stcd, tm,'+@sql+
'FROM (
SELECT stcd, CONVERT(VARCHAR(10), TM, 20) tm,'+@s1+'
FROM st_rain_s
WHERE stcd = '''+@stcd+'''
AND TM BETWEEN '''+@begin+''' AND '''+@end+'''
GROUP BY stcd, CONVERT(VARCHAR(10), TM, 20)
) AS t
')
GOEXEC p_test '90800001','2010-7-7 20:07:44','2010-7-9 21:07:52',3
/*
stcd tm 1列最大值 2列最大值 3列最大值
-------- ---------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4 7 9
90800001 2010-07-09 10 14 17(2 行受影响)
*/
--SQL2000
SELECT stcd, tm, (
SELECT MAX(p)
FROM (
SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11
) a
) AS 一列最大值, (
SELECT SUM(p)
FROM (
SELECT TOP 2 p
FROM (
SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11
) a
ORDER BY 1 DESC
) b
) AS 两列最大值, (
SELECT SUM(p)
FROM (
SELECT TOP 3 p
FROM (
SELECT p = p8 UNION SELECT p9 UNION SELECT p10 UNION SELECT p11
) a
ORDER BY 1 DESC
) b
) AS 三列最大值
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 sql2005执行没问题,2000有问题。
你的参数:@n INT --统计最多的列数如果n=24,是不是1--24列的和都显示出来,如果这样就不是我想要的。
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 DEC(18,2)
,p8 DEC(18,2)
,p9 DEC(18,2)
,p10 DEC(18,2)
,p11 DEC(18,2)
)
insert st_rain_s
select '90800001','2010-7-8 21:02:58',10,4,2,3,4
union all
select '90800001','2010-7-9 21:02:58',20,10,3,3,4CREATE FUNCTION GET_MAXSUM(@stcd char(8),@TM datetime,@COLNUM INT=1)
RETURNS INT
AS
BEGIN
DECLARE @SUM INT
SET @SUM=
(
SELECT SUM(RN)
FROM(
SELECT T.*,COUNT(T1.CNT)+1 AS COLNUM
FROM
(SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) t
LEFT JOIN
(SELECT STCD,TM,P8 AS RN ,1 as CNT FROM st_rain_s UNION ALL SELECT STCD,TM,P9,2 FROM st_rain_s UNION ALL SELECT STCD,TM,P10,3 FROM st_rain_s UNION ALL SELECT STCD,TM,P11,4 FROM st_rain_s) T1
ON T1.stcd=T.stcd and datediff(DD,T.tm,T1.tm)=0 and (T1.rn>t.rn or (T1.rn=t.rn and T1.cnt>T.cnt))
GROUP BY T.STCD,T.TM,T.RN,T.CNT
) T WHERE STCD=@STCD AND DATEDIFF(DD,TM,@TM)=0 AND COLNUM<=@COLNUM
)
RETURN @SUM
END
SELECT STCD,TM,DYP,DBO.GET_MAXSUM(STCD,TM,1),DBO.GET_MAXSUM(STCD,TM,2),DBO.GET_MAXSUM(STCD,TM,3),DBO.GET_MAXSUM(STCD,TM,4) FROM st_rain_s/*
STCD TM DYP
-------- ------------------------------------------------------ -------------------- ----------- ----------- ----------- -----------
90800001 2010-07-08 21:02:58.000 10.00 4 8 11 13
90800001 2010-07-09 21:02:58.000 20.00 10 14 17 20(所影响的行数为 2 行)*/
写了个函数,楼主试试吧,没05的方便