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

net_xiaojian 2010-07-10 06:50:55 


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 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，两列连续相加的值。
...全文
238 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
net_xiaojian 2010-07-11
  • 打赏
  • 举报
 回复 


--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里执行不了
htl258_Tony 2010-07-10
  • 打赏
  • 举报
 回复 
--> 生成测试数据表: [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 

')

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

*/
我也简单做个动的。
永生天地 2010-07-10
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 xys_777 的回复:]
再次扩展


SQL code
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
)
……
[/Quote]
贴多了，改改

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 int



create 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 #t



set @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 cur



set @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 行受影响)



*/
永生天地 2010-07-10
  • 打赏
  • 举报
 回复
再次扩展

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 int



create 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 #t



set @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 cur



set @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
claro 2010-07-10
  • 打赏
  • 举报
 回复
纯帮顶
htl258_Tony 2010-07-10
  • 打赏
  • 举报
 回复 
--> 生成测试数据表: [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'



--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的方法也加上
htl258_Tony 2010-07-10
  • 打赏
  • 举报
 回复 
--> 生成测试数据表: [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 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以上版本，可以用此代码。
net_xiaojian 2010-07-10
  • 打赏
  • 举报
 回复
两列、三列相加最大值是取p8,p9,p10,p11，两列连续相加的值、三列连续相加最大的值，请看测试数据。
pandas操作2（增加列并赋值/两列时间相减/按时间合并且求和）
pandas常用csv操作增加一列数据并赋值两列时间相减按时间合并且求和 增加一列数据并赋值 有时候可能会遇到需要增加一列数据，例如增加一列全为1的数据，方便按时间合并行过后统计次数。 df['xxx number'] = 1 两列时间相减 使用pandas求两列时间的差，也就是统计csv数据集种某个任务持续的时间 df['end time'] = pd.to_datetime(df['end time']) df['start time'] = pd.to_datetime(df['start time
使用SQL统计表格的最大值、最小值、平均值和中位数值(PostgreSQL)
类似地，要计算表格中某一列的最小值，我们可以使用SQL中的MIN函数。要计算表格中某一列的最大值，我们可以使用SQL中的MAX函数。这会返回一个名为"median_salary"的结果列，其中包含了"salary"列的中位数值。这会返回一个名为"max_salary"的结果列，其中包含了"salary"列的最大值。这会返回一个名为"min_salary"的结果列，其中包含了"salary"列的最小值。这会返回一个名为"avg_salary"的结果列，其中包含了"salary"列的平均值。
有两列，按照其中的一列分组，对另一列统计，转为list形式
不规则长度字典转DataFrame，分组聚合，字典按key 追加值。 往往字典在实际中能解决很多问题，而且很灵活，我们重点掌握字典的一些用法。
【DataFrame】统计列中重复值
举个简单地栗子： import pandas as pd x=[{'id': 2, 'name':1,'value':1},{'id': 1, 'name':1,'value':2},{'id': 4, 'name':1,'value':3},{'id': 3, 'name':1,'value':4}] p =pd.DataFrame(x) 运行结果： 1、只看结果 # 一列，比如查看name...
python统计某列不同值的个数_pandas数据框，统计某列或者某行数据元素的个数
版权声明：本文为博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。本文链接：https://blog.csdn.net/sinat_38893241/article/details/80414977在《pandas数据框，统计某列数据与其他文件对应关系的个数》之后，我发觉简单版的元素个数统计问题没有说清楚，就在这里介绍两个统计pandas数据框里面列、行元素个...
疑难问题

22,210

社区成员

121,730

社区内容

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

加载中

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

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

+ 用AI写文章