C# 中更新表的 UDF（将所有数据除以查询结果）

Question

有没有办法将表的每个数据（所有列和行）除以

select count(*) from table;

例如

表

     a1  a2  a3   a4
   -------------------
    438 498 3625 3645
    500 291 5000 2351
    233 263 1298 1687
    198 117 1744 5932
    438 498 3625 3648
    500 291 5000 2637

，然后将每个数据除以 6（行数）

     a1    a2   a3      a4
   ------------------------
   73.0  83.0 604.16  607.5
   83.33 48.5 833.33  391.83
   ...
   ...
   ...
   ...

一个问题是我的数据是 INT，但转换后它们会是双重的...

在 c# 中我正在做：

int N = 0;
string sql = @"select count(*) from 'ExampleTable'";
N  = (int)cmd.ExecuteScalar();

int Columns = 0;
sql = @"select count(*) from information_schema.COLUMNS WHERE TABLE_NAME = 'ExampleTable'";

Columns  = (int)cmd.ExecuteScalar();


cmdLoad.CommandText = "SELECT * FROM [ExampleTable]";
int i,j;
double[,] newTable  = new double[N, Columns];
using (SqlDataReader reader = cmdLoad.ExecuteReader())
{
   while (reader.Read())
    {
        for ( i = 1; i <= N; j++)
        {
           for ( j = 1; j <= Columns; j++)
          {
              newTable[i,j] = reader.GetDouble((i*j) - 1) / N ;
          } //j  
        } //i
     }//reader
}

但我不确定...

Answer 1

如果您提前知道这些列，则非常简单：

SELECT 
    a1 / Rows As a1, 
    a2 / Rows As a2, 
    a3 / Rows As a3, 
    a4 / Rows As a4
FROM 
(
    SELECT *, CAST(COUNT(*) OVER (PARTITION BY NULL) as float) As Rows
    FROM ExampleTable
) Data

如果不知道，则需要有效地执行在 C# 中所做的操作 - 循环遍历列并构建动态查询

DECLARE @TableName varchar(100)
DECLARE @Query varchar(max)
SELECT @TableName = 'ExampleTable'
SELECT @Query = 'SELECT '
SELECT @Query = @Query + '[' + COLUMN_NAME + '] / Rows as [' + COLUMN_NAME + '], '
FROM information_schema.COLUMNS col WHERE TABLE_NAME = @TableName

SELECT @Query = @Query + 'Rows FROM ( SELECT *, CAST(COUNT(*) OVER (PARTITION BY NULL) as float) As Rows FROM [' + @TableName + ']) Data'
EXECUTE(@Query)