表 sql server 中具有最大整数值的列 [英] column having maximum integer value from table sql server

问题描述

此查询返回表中的最大整数值:

This query returns me the maximum integer value from a table:

  SELECT rownum, max(col) as maxValue -- min 10717 , max = 311216
 FROM
  (
   SELECT rownum, col FROM Tables1
    UNPIVOT
   (col FOR ListofColumns IN 

  (col1,col2,col3,col4,col5, col6))
   AS unpivott) AS p
GROUP BY rownum

代替col1,col2,col3,col4,col5,col6".我想让这个动态(从数据类型为 int/numeric 的表中自动获取).

Instead of "col1,col2,col3,col4,col5,col6". I would like to make this dynamic(automatically taken from table having data type int/numeric).

注意:有时列数可能是 50，我不想写每个列名.

Note: Sometimes number of columns could be 50 and I don't want to write each and every column names.

我该怎么做?

推荐答案

动态 sql 是唯一的方法.你这样开始，

Dynamic sql is the only way. you start like this,

    create  table t1(rolname varchar(20),col1 int,col2 int,col3 int,col4 int)
insert into t1 values('a',32,45,23,645)
,('b',67,34,2673,344),('c',423,767,54,343),('d',676,43435,3432,4343)


declare @col varchar(2000)
select top 1
@col=stuff((select ','+'['+COLUMN_NAME+']' from INFORMATION_SCHEMA.COLUMNS b
where TABLE_NAME=a.TABLE_NAME and  DATA_TYPE='int'  for XML path('') ),1,1,'')
from INFORMATION_SCHEMA.COLUMNS a
where TABLE_NAME='t1'

select @col

declare @sql varchar(max)
set @sql='sELECT rolname, max(col)col
 FROM
  (
   SELECT * FROM t1
    UNPIVOT
   (col FOR ListofColumns IN 

  ( '+@col+'))
   AS unpivott) AS p
   GROUP BY rolname'

   exec (@sql)

这篇关于表 sql server 中具有最大整数值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！