sql - 如何创建一个表来表示给定表中所有列的最小值和最大值？

Question

我想知道是否可以创建如下汇总表。例如，给定一个表

Id | Age | Banksaldo | Number of children | Car owner
1  | 27  | 2000      | 5                  | No
2  | 30  | 3000      | 2                  | Yes

应该变成:

Column name | Minimum | Minimum Id | Maximum | Maximum Id
Age         | 27      | 1          | 30      | 2
Banksaldo   | 2000    | 1          | 3000    | 2
Number of...| 2       | 2          | 5       | 1

具体来说，我在调用 SQL 语句然后将其插入新表时遇到问题。另外，我在按照上面指定的格式创建它时也遇到了问题。

有人可以给我一些关于如何解决这个问题的提示吗？

感谢您的努力。

PS:顺便说一下，我使用的是 Microsoft SQL Server 2014。

Answer 1

首先，您可以使用 UNPIVOT:

 SELECT Id, [Column Name], Columns
   FROM 
     (SELECT Id, Age, Banksaldo, [Number of children]
      FROM mytable) p
   UNPIVOT
      (Columns FOR [Column Name] IN (Age, Banksaldo, [Number of children])
   )AS unpvt 

得到以下结果集:

Id  Column Name         Columns
--------------------------------
1   Age                 27
1   Banksaldo           2000
1   Number of children  5
2   Age                 30
2   Banksaldo           3000
2   Number of children  2

在上面的派生表上使用ROW_NUMBER来定位最小/最大记录:

SELECT Id, [Column Name], Columns,
       ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                          ORDER BY Columns) AS minRn,
       ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                          ORDER BY Columns DESC) AS maxRn
FROM (
  ... unpivot query here ...
) t 

上面的输出:

 Id Column Name         Columns minRn   maxRn
-----------------------------------------------
2   Age                 30      2       1
1   Age                 27      1       2
2   Banksaldo           3000    2       1
1   Banksaldo           2000    1       2
1   Number of children  5       2       1
2   Number of children  2       1       2

最后一步，您可以对 minRn、maxRn 使用条件聚合来获取所需的结果集:

SELECT [Column Name], 
       MAX(CASE WHEN minRn = 1 THEN Id END) AS [Minimum Id],
       MAX(CASE WHEN minRn = 1 THEN Columns END) AS [Minimum],
       MAX(CASE WHEN maxRn = 1 THEN Id END) AS [Maximum Id],
       MAX(CASE WHEN maxRn = 1 THEN Columns END) AS [Maximum]
FROM ( 
  SELECT Id, [Column Name], Columns, minRn, maxRn
  FROM (
  SELECT Id, [Column Name], Columns,
         ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                            ORDER BY Columns) AS minRn,
         ROW_NUMBER() OVER (PARTITION BY [Column Name] 
                            ORDER BY Columns DESC) AS maxRn
  FROM (
     SELECT Id, [Column Name], Columns
     FROM 
       (SELECT Id, Age, Banksaldo, [Number of children]
        FROM mytable) p
     UNPIVOT
        (Columns FOR [Column Name] IN (Age, Banksaldo, [Number of children])
     )AS unpvt ) t 
) s
WHERE s.minRn = 1 OR s.maxRn = 1 ) u
GROUP BY [Column Name]

SQL Fiddle Demo