最高值和最低值

最高值和最低值

问题描述：

我得到我的所有列细节象下面这样：最高值和最低值

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'table1' 
    AND DATA_TYPE IN ('int','decimal','numeric')

我的表结构如下图所示：

CREATE TABLE [dbo].[table1](
[col1] int not NULL, 
[col2] [numeric](7, 0) NULL, 
[col3] [varchar](30) NULL, 
[col4] [varchar](8) NULL, 
[col5] [varchar](2) NULL, 
[col6] [varchar](8) NULL, 
[col7] [varchar](3) NULL, 
[col8] [varchar](7) NULL, 
[col9] [varchar](5) NULL, 
[col10] [varchar](8) NULL, 
[col11] [numeric](7, 0) NULL, 
[col12] [numeric](7, 3) NULL, 
[col13] [numeric](7, 2) NULL, 
[col14] [decimal](7, 2) NULL, 
[col15] [varchar](1) NULL, 
) ON [PRIMARY]

下面是几个例子值时，我“M查询表1

col1 col2  col11  col12  col13  col14 
------------------------------------------------------------ 
1  10.0  80.00  10.000  12.00  90.00 
2  70.0  10.00  97.960  14.00  10.00 
3  30.00  12.00  14.000  115.00  11.00 
4  40.00  11.00  15.000  15.80  12.00

我想我的结果是这样的：

for max: 

Table_name max_col_name  max_col_value  max_col_value_length 
--------------------------------------------------------------------- 
table1  col3    115.00    6 


for min value: 

Table_name min_col_name  min_col_value  min_col_value_length 
--------------------------------------------------------------------- 
table1  col1    1     1

我该如何达到这个目标？

我想你会需要一些动态SQL这里，因为它找到了最小值/最大值查询本身值必须在飞行中生成。 –

你想解决什么问题？因为像SQL这样的任务在SQL之外的代码层可能会更好地处理，因为它不是一个真正的关系操作？ –

我有超过20个Millon记录的表格，而不是逐个搜索和查找，我想要一些查询来给出结果。 – AskMe

答

您可以使用下面的代码来获得所需的结果。如果列名是动态的，那么你可以很容易地将下面的代码转换为动态SQL。

CREATE TABLE [dbo].[table1](
[col1] int not NULL, 
[col2] [numeric](7, 0) NULL, 
[col3] [varchar](30) NULL, 
[col4] [varchar](8) NULL, 
[col5] [varchar](2) NULL, 
[col6] [varchar](8) NULL, 
[col7] [varchar](3) NULL, 
[col8] [varchar](7) NULL, 
[col9] [varchar](5) NULL, 
[col10] [varchar](8) NULL, 
[col11] [numeric](7, 0) NULL, 
[col12] [numeric](7, 3) NULL, 
[col13] [numeric](7, 2) NULL, 
[col14] [decimal](7, 2) NULL, 
[col15] [varchar](1) NULL, 
) ON [PRIMARY] 
GO 

insert into table1 (col1,col2,col11,col12,col13,col14) values 
(1,  10.0 , 80.00,  10.000 , 12.00 , 90.00), 
(2,  70.0 , 10.00 , 97.960 , 14.00  , 10.00), 
(3,  30.00,  12.00 , 14.000 , 115.00 , 11.00), 
(4,  40.00,  11.00 , 15.000  , 15.80 , 12.00) 

declare @sql varchar(max) 
declare @col varchar(max)='' 
select @[email protected]+ COLUMN_NAME +', ' FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'table1' 
    AND DATA_TYPE IN ('int','decimal','numeric') 

SELECT ColumnName,ColumnValue 
into #TableMax 
from (select cast(max(col1) as numeric(7,3))C1, CAST(max(col2) as numeric(7,3))C2, 
              cast(max(col11) as numeric(7,3))C11, CAST(max(col12) as numeric(7,3))C12, 
              CAST(max(col13) as numeric(7,3))C13, CAST(Max(col14) as numeric(7,3))C14 

from table1) T 
Unpivot(ColumnValue For ColumnName IN (C1,C2,C11,C12,C13,C14)) AS H 

SELECT ColumnName,ColumnValue 
into #TableMin 
from (select cast(Min(col1) as numeric(7,3))C1, CAST(Min(col2) as numeric(7,3))C2, 
              cast(Min(col11) as numeric(7,3))C11, CAST(Min(col12) as numeric(7,3))C12, 
              CAST(Min(col13) as numeric(7,3))C13, CAST(Min(col14) as numeric(7,3))C14 

from table1) T 
Unpivot(ColumnValue For ColumnName IN (C1,C2,C11,C12,C13,C14)) AS H 

select top 1 columnname max_columnname,columnvalue max_columnvalue,len(columnvalue)max_col_value_length from #TableMax order by columnvalue desc 
select top 1 columnname min_columnname,columnvalue min_columnvalue,len(columnvalue)min_col_value_length from #TableMin order by columnvalue 
drop table table1 
drop table #TableMax 
drop table #TableMin

答

检查：

DECLARE @TB TABLE(col1 int, col2 decimal(10,2), col11 decimal(10,3), col12 decimal(10,2),col13 decimal(10,2),col14 decimal(10,2)) 
insert into @tb 
select 1 ,  10.0 , 80.00 , 10.000 , 12.00 , 90.00 union all 
select 2 ,  70.0 , 10.00 , 97.960 , 14.00 , 10.00 union all 
select 3 ,  30.00 , 12.00 , 14.000 , 115.00 , 11.00 union all 
select 4 , 40.00 , 11.00 , 15.000 ,  15.80 , 12.00 





select 'table1' as Table_Name,b.column_name as max_col_name, cast(a.max_col as decimal(10,2)) as max_col_value , len(cast(a.max_col as decimal(10,2))) as max_col_value_length from 
(select max(max_col1) as max_col from 
(select max(col1) as max_col1,'col1' as column_name from @tb union all 
select max(col2) as max_col2,'col2' from @tb union all 
select max(col11)as max_col11,'col11' from @tb union all 
select max(col12)as max_col12,'col12' from @tb union all 
select max(col13)as max_col13,'col13' from @tb union all 
select max(col14)as max_col14,'col14' from @tb) as a) as a 

left join 

(select max(max_col1) as max_col,column_name from 
(select max(col1) as max_col1,'col1' as column_name from @tb union all 
select max(col2) as max_col2,'col2' from @tb union all 
select max(col11)as max_col11,'col11' from @tb union all 
select max(col12)as max_col12,'col12' from @tb union all 
select max(col13)as max_col13,'col13' from @tb union all 
select max(col14)as max_col14,'col14' from @tb) as a group by column_name) as b 
on a.max_col = b.max_col 








select 'table1' as Table_Name,b.column_name as min_col_name, cast(a.min_col as integer) as min_col_value , len(cast(a.min_col as integer)) min_col_value_length from 
(select min(min_col1) as min_col from 
(select min(col1) as min_col1,'col1' as column_name from @tb union all 
select min(col2) as min_col2,'col2' from @tb union all 
select min(col11)as min_col11,'col11' from @tb union all 
select min(col12)as min_col12,'col12' from @tb union all 
select min(col13)as min_col13,'col13' from @tb union all 
select min(col14)as min_col14,'col14' from @tb) as a) as a 

left join 

(select min(min_col1) as min_col,column_name from 
(select min(col1) as min_col1,'col1' as column_name from @tb union all 
select min(col2) as min_col2,'col2' from @tb union all 
select min(col11)as min_col11,'col11' from @tb union all 
select min(col12)as min_col12,'col12' from @tb union all 
select min(col13)as min_col13,'col13' from @tb union all 
select min(col14)as min_col14,'col14' from @tb) as a group by column_name) as b 
on a.min_col = b.min_col

结果为最大：

table1 col13 115.00 6

结果为分：

table1 col1 1 1

谢谢。但是，我有列表的列数在10 - 50之间变化。这是一些表格有10列，有些列有50列。可以变动吗？ – AskMe

这都是你的，如果你有很多列......你可以编辑上面的答案并添加你想要的其他列。重要的是你已经有一个指导如何做到这一点。 –

答

我认为你可以用两个步骤实现这一点，首先然后搜索每列的最大值或最小值不旋转它（旋转表格，使列成为行，成为列）找到整体的最大值或最小值。

此查询将查找最大值

WITH cte1 AS (SELECT 'table1' AS Table_name 
         ,max_col_name 
         ,max_col_value 
         ,LEN(max_col_value) AS max_col_value_length 
       FROM 
        (SELECT col2,col3,col4 
        FROM 
        (SELECT MAX(col2) AS col2 
          ,MAX(col3) AS col3 
          ,MAX(col4) AS col4 FROM table1) x) y 
        UNPIVOT 
        (Nilai FOR max_col_name IN (col2,col3,col4)) unpvt) 

SELECT * FROM cte 
WHERE max_col_value = (SELECT MAX(max_col_value) FROM cte)

要找到最小值只是改变所有最大与最小值

相关推荐