【2010-07-10】统计最大一列值，最大两列相加值。

Question

create database maxColumn
use maxColumncreate table st_rain_s
(
stcd char(8)
,TM datetime
,DYP float
,p8 float
,p9 float
,p10 float
,p11 float
)select * from st_rain_sinsert 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 datetimeset @stcd='90800001'
set @begin='2010-7-7 20:07:44'
set @end='2010-7-9 21:07:52'--单列最大值
SELECT stcd,CONVERT(VARCHAR(10), TM, 20) tt,MAX(P8) h1 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 where stcd=@stcd and TM between @begin and @end
GROUP BY STCD,CONVERT(VARCHAR(10), TM, 20)--数据显示如下
stcd             TM             单列最大值     两列相加最大值      三列相加最大值
90800001    2010-07-08            4.0            7.0                    9
90800001    2010-07-09            10.0           14.0                   16两列、三列相加最大值是取p8,p9,p10,p11，两列连续相加的值。

Answer 1

两列、三列相加最大值是取p8,p9,p10,p11，两列连续相加的值、三列连续相加最大的值，请看测试数据。

Answer 2

--> 生成测试数据表: [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    DATETIMESET @stcd = '90800001'
SET @begin = '2010-7-7 20:07:44'
SET @end = '2010-7-9 21:07:52';WITH t AS 
(
SELECT rn = ROW_NUMBER()OVER(PARTITION BY stcd, tm ORDER BY val DESC) ,*
FROM (
 SELECT stcd, CONVERT(VARCHAR, tm, 23) tm, p8, p9, p10, p11 
 FROM st_rain_s
 WHERE stcd = @stcd
          AND tm BETWEEN @begin AND @end
 ) a
 UNPIVOT(val FOR col IN (p8, p9, p10, p11)) b
)
SELECT stcd,tm,
一列最大值=SUM(CASE WHEN rn=1 THEN val ELSE 0 END),
两列最大值=SUM(CASE WHEN rn<=2 THEN val ELSE 0 END),
三列最大值=SUM(CASE WHEN rn<=3 THEN val ELSE 0 END)                 
FROM t
GROUP BY stcd,tm
/*
stcd     tm                             一列最大值                  两列最大值                  三列最大值
-------- ------------------------------ ---------------------- ---------------------- ----------------------
90800001 2010-07-08                     4                      7                      9
90800001 2010-07-09                     10                     14                     17(2 行受影响)
*/如果楼主升到SQL2005以上版本，可以用此代码。

Answer 3

--> 生成测试数据表: [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    DATETIMESET @stcd = '90800001'
SET @begin = '2010-7-7 20:07:44'
SET @end = '2010-7-9 21:07:52'--SQL2005
;WITH t AS 
(
SELECT rn = ROW_NUMBER()OVER(PARTITION BY stcd, tm ORDER BY val DESC) ,*
FROM (
 SELECT stcd, CONVERT(VARCHAR, tm, 23) tm, p8, p9, p10, p11 
 FROM st_rain_s
 WHERE stcd = @stcd
          AND tm BETWEEN @begin AND @end
 ) a
 UNPIVOT(val FOR col IN (p8, p9, p10, p11)) b
)
SELECT stcd,tm,
一列最大值=SUM(CASE WHEN rn=1 THEN val ELSE 0 END),
两列最大值=SUM(CASE WHEN rn<=2 THEN val ELSE 0 END),
三列最大值=SUM(CASE WHEN rn<=3 THEN val ELSE 0 END)                 
FROM t
GROUP BY stcd,tm--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 /*
stcd     tm         一列最大值                  两列最大值                  三列最大值
-------- ---------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4                      7                      9
90800001 2010-07-09 10                     14                     17(2 行受影响)
*/2000的方法也加上

Answer 4

再次扩展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
)
go
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
go
--测试
if object_id('p_test') is not null drop proc P_test
go
create proc p_test
 @stcd char(8)
, @begin datetime
, @end datetime
, @n int
as
begin
if @n<1 return
declare @sql varchar(8000),@sql1 varchar(8000)
declare @i int,@id int,@name varchar(200),@count int,@j intcreate table #t (id int identity(0,1),name varchar(200))
insert #t
select name from syscolumns t
where id=object_id('st_rain_s')
and name not in('stcd','tm','dyp')
order by colid
select @count=count(1) from #tset @sql=''declare cur cursor for select * from #t
open cur
fetch cur into @id,@name
while @@fetch_status=0
begin
set @sql=@sql+'SELECT stcd,TM'
set @i=1

while @i<=@n
begin
set @sql=@sql+','+@name
set @j=1
while @j<@i
begin
select @sql=@sql+'+'+name from #t where id=(@id+@j)%@count
set @j=@j+1
end
set @sql=@sql+' as P'+ltrim(@i)+' '
set @i=@i+1
end
set @sql=@sql+' from  st_rain_s UNION ALL '
fetch cur into @id,@name
end
close cur
deallocate curset @i=1
set @sql1='SELECT stcd,CONVERT(VARCHAR(10), TM, 20) tm'
while @i<=@n
begin
set @sql1=@sql1+',max(p'+ltrim(@i)+') ['+ltrim(@i)+'列相加最大值] '
set @i=@i+1
end
set @sql1=@sql1+' FROM ('
+left(@sql,len(@sql)-10)+')t where stcd='''+@stcd+''' and TM between '''
+cast(@begin as varchar)+''' and '''+cast(@end as varchar)+'''
GROUP BY STCD,CONVERT(VARCHAR(10), TM, 20)'
exec( @sql1)
end
go--调用
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'exec p_test @stcd,@begin,@end,3
/*
stcd     tm         1列相加最大值                2列相加最大值                3列相加最大值
-------- ---------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4                      7                      9
90800001 2010-07-09 10                     14                     17(2 行受影响)*/--调用
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'exec p_test @stcd,@begin,@end,10
/*(4 行受影响)
stcd     tm         1列相加最大值                2列相加最大值                3列相加最大值                4列相加最大值                5列相加最大值                6列相加最大值                7列相加最大值                8列相加最大值                9列相加最大值                10列相加最大值
-------- ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4                      7                      9                      10                     14                     17                     19                     20                     24                     27
90800001 2010-07-09 10                     14                     17                     20                     30                     34                     37                     40                     50                     54(2 行受影响)*/--非要那个结果的这样试试
select @sql=@sql+','+name+' as o,'+name+'+'+
isnull((select name+' as P from  st_rain_s UNION ALL SELECT stcd,TM' from  syscolumns 
where id= t.id and colid=t.colid+1
and name not in('stcd','tm','dyp')),
(select top 1 name +' as P from  st_rain_s ' from  syscolumns 
where id= t.id and name not in('stcd','tm','dyp') 
order by colid))from syscolumns t
where id=object_id('st_rain_s')
and name not in('stcd','tm','dyp')
order by colid
 set @sql='SELECT stcd,CONVERT(VARCHAR(10), TM, 20) tt,max(o)单列最大值,MAX(P) 两列相加最大值 FROM ('
+@sql+')t where stcd='''+@stcd+''' and TM between '''
+cast(@begin as varchar)+''' and '''+cast(@end as varchar)+'''
GROUP BY STCD,CONVERT(VARCHAR(10), TM, 20)'
exec( @sql)/*
stcd     tt         单列最大值                  两列相加最大值
-------- ---------- ---------------------- ----------------------
90800001 2010-07-08 4                      7
90800001 2010-07-09 10                     14(2 行受影响)*/select 13%13

Answer 5

贴多了，改改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
)
go
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
go
--测试
if object_id('p_test') is not null drop proc P_test
go
create proc p_test
 @stcd char(8)
, @begin datetime
, @end datetime
, @n int
as
begin
if @n<1 return
declare @sql varchar(8000),@sql1 varchar(8000)
declare @i int,@id int,@name varchar(200),@count int,@j intcreate table #t (id int identity(0,1),name varchar(200))
insert #t
select name from syscolumns t
where id=object_id('st_rain_s')
and name not in('stcd','tm','dyp')
order by colid
select @count=count(1) from #tset @sql=''declare cur cursor for select * from #t
open cur
fetch cur into @id,@name
while @@fetch_status=0
begin
set @sql=@sql+'SELECT stcd,TM'
set @i=1

while @i<=@n
begin
set @sql=@sql+','+@name
set @j=1
while @j<@i
begin
select @sql=@sql+'+'+name from #t where id=(@id+@j)%@count
set @j=@j+1
end
set @sql=@sql+' as P'+ltrim(@i)+' '
set @i=@i+1
end
set @sql=@sql+' from  st_rain_s UNION ALL '
fetch cur into @id,@name
end
close cur
deallocate curset @i=1
set @sql1='SELECT stcd,CONVERT(VARCHAR(10), TM, 20) tm'
while @i<=@n
begin
set @sql1=@sql1+',max(p'+ltrim(@i)+') ['+ltrim(@i)+'列相加最大值] '
set @i=@i+1
end
set @sql1=@sql1+' FROM ('
+left(@sql,len(@sql)-10)+')t where stcd='''+@stcd+''' and TM between '''
+cast(@begin as varchar)+''' and '''+cast(@end as varchar)+'''
GROUP BY STCD,CONVERT(VARCHAR(10), TM, 20)'
exec( @sql1)
end
go--调用
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'exec p_test @stcd,@begin,@end,3
/*
stcd     tm         1列相加最大值                2列相加最大值                3列相加最大值
-------- ---------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4                      7                      9
90800001 2010-07-09 10                     14                     17(2 行受影响)*/--调用
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'exec p_test @stcd,@begin,@end,10
/*(4 行受影响)
stcd     tm         1列相加最大值                2列相加最大值                3列相加最大值                4列相加最大值                5列相加最大值                6列相加最大值                7列相加最大值                8列相加最大值                9列相加最大值                10列相加最大值
-------- ---------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
90800001 2010-07-08 4                      7                      9                      10                     14                     17                     19                     20                     24                     27
90800001 2010-07-09 10                     14                     17                     20                     30                     34                     37                     40                     50                     54(2 行受影响)*/

Answer 6

--> 生成测试数据表: [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查询如下:
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 行受影响)
*/我也简单做个动的。

Answer 7

--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 上面贴错了，这个在2005里面执行没有问题。2000里执行不了