包含超过20列的表的各列求和而不写出总和

Question

所以我需要在我正在使用的表中获取20多个列的总和，我真的很想不必写出每一列的总和，但是我不确定这是可能的。有没有办法让它为表中的所有列动态写入？

select isnull(osname, 'Total'), COUNT(*), SUM(col1), SUM(col2), SUM(col3)...sum(col27)
from usage
group by osname
with rollup

Answer 1

首先，如果可以自动化，请这样做。输入20、100列绝对是乏味的。

我下面的解决方案使用表变量函数来获取编号列的列表。这可以再次重用。

其次，我使用此函数并应用您的逻辑来创建一些动态的T-SQL。WITH ROLLUP语法不符合ISO。在2008 R2中已将其替换。

http://technet.microsoft.com/zh-
CN/library/ms177673.aspx

让我们谈正事吧。我喜欢创建一个快速测试表以确保我的语法正确。甚至我也会犯错。

--
-- Setup test data
--

-- Just playing around
use tempdb;
go

-- Drop test table
if object_id ('usage') > 0 
drop table usage
go

-- Create test table
create table usage
( osname varchar(16),
  col1 int,
  col2 int,
  col3 int,
  col4 int,
  col5 int
);
go

-- Test data
insert into usage values
('UNIX', 1, 2, 3, 4, 5),
('UNIX', 2, 4, 6, 8, 10),
('WIN7', 1, 2, 3, 4, 5),
('WIN7', 2, 4, 6, 8, 10),
('WIN8', 5, 10, 15, 20, 25);
go

-- Show the data
select * from usage;
go

接下来，让我们创建一个内联表值函数。它们相对较快。

http://blog.waynesheffield.com/wayne/archive/2012/02/comparing-inline-and-
multistatement-table-valued-
functions/

--
-- Create helper function
--

-- Remove ITVF
if object_id('get_columns') > 0
drop function get_columns
go

-- Create ITVF
create function get_columns (@table_name sysname)
returns table
as
return 
(
  select top 100 column_id, name from sys.columns where object_id in
  (  select object_id from sys.tables t where t.name = @table_name )
  order by column_id
)
go

-- Get the columns
select * from get_columns('usage');
go

现在，将它们放在一起解决您的问题。注意，您需要保留被遗漏的列名。我还改用了符合ISO的语法。

--
-- Solve the problem
--

-- Dynamic SQL
declare @var_tsql nvarchar(max) = 'SELECT ';
select 
  @var_tsql += 
    case 
      when column_id <> 1 then 'SUM(' + name + ') as s_' + name + ', ' 
      else 'ISNULL(' + name + ', ''Total'') as s_name, '
    end 
from get_columns('usage');
select @var_tsql += 'COUNT(*) as s_count FROM usage GROUP BY ROLLUP(osname); '

--print @var_tsql
EXEC sp_executesql @var_tsql;

print语句用于调试任何语法错误。剪切并粘贴到另一个窗口中以检查语法。您学得越多，动态SQL就越容易。

上面的动态TSQL可以正常工作。这是您的输出。