[htl258]求助。

[net_xiaojian]

http://topic.csdn.net/u/20100710/06/cffaa7af-391d-4cf9-8727-0f366db55218.html

您昨天回答的帖子，sql2000执行的有问题，提示：列名 'p8' 无效。这些列都无效，请你再看看，谢谢。

[永生天地]

他的那个解法里有bug

[SQL77]

你的表是啥,?????????列名都无效的话找表的原因

[net_xiaojian]

他那两种方法在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这个是多少列相加，我这里不合适，

[SQL77]

[Quote=引用 2 楼 net_xiaojian 的回复:]
他那两种方法在sql2005里面没有问题，2000里面有问题

SQL code

SELECT MAX(p)
FROM (
SELECT p = p8 from st_rain_s UNION all
SELECT p9 from st_rain_s UNION all SELECT p10 f……
[/Quote]可以写个函数楼主

[永生天地]

--> 生成测试数据表: [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 
')
GO

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

[net_xiaojian]

--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有问题。

[net_xiaojian]

xys_77
你的参数：@n INT --统计最多的列数

如果n=24，是不是1--24列的和都显示出来，如果这样就不是我想要的。

[永生天地]

杯具了

[Quote=引用 2 楼 net_xiaojian 的回复:]
他那两种方法在sql2005里面没有问题，2000里面有问题

SQL code

SELECT MAX(p)
FROM (
SELECT p = p8 from st_rain_s UNION all
SELECT p9 from st_rain_s UNION all SELECT p10 f……
[/Quote]

[永生天地]

[Quote=引用 7 楼 net_xiaojian 的回复:]
xys_77
你的参数：@n INT --统计最多的列数

如果n=24，是不是1--24列的和都显示出来，如果这样就不是我想要的。
[/Quote]
那，你要的什么结果

[claro]

。。。活佛救他。

[net_xiaojian]

1个字段，6个字段，12个字段，24个字段相加。

[永生天地]

[Quote=引用 11 楼 net_xiaojian 的回复:]
1个字段，6个字段，12个字段，24个字段相加。
[/Quote]
参数改改，循环改改，就可以了

[永生天地]

lz难道不需要求连续的字段的加和了。

[SQL77]

--> 生成测试数据表: [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,4

CREATE 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的方便

[htl258_Tony]

不好意思，才看到，好像77已经解决了。建议升到2005以上版本，体验强大的功能。