动态表列的总和 [英] sum of dynamic tables column

问题描述

我是sql server的新手。我最近下载了Sqlserver 2008并从vb.net连接。我正在尝试使用两个列创建动态表格

，例如

名称，通过从表格中传递表格

名称。

I am new to sql server .I have recently downloaded Sqlserver 2008 and connected from vb.net . I am trying to create dynamic tables
with two columns such as
name,amount by passing table
names from text box .

CREATE TABLE " & TblName & "( [Name] TEXT(10), [Amount] TEXT(6))

table1
-------------
name | amount
   a | 40
   b | 60

   table2
---------------
name | amount
   a | 150
   b | 50

我想显示这样的输出

I want to display output like this

table name| total
   table1 | 100
   table2 | 200

我可以使用以下查询检索表名

I can retrieve table names using the below query

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

我已尝试过查询但

它显示一个空列

I have tried the query but
it displays a empty column

SELECT 'SELECT ISNULL(SUM('+COLUMN_NAME + '),0) AS a FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount'

我会想知道

是否可以在不指定表名的情况下编写查询？

I would like to know
Is it possible to write a query without specifying table name?

推荐答案

尝试

try

declare @sql Nvarchar(max)
select @sql='select * from ('+  STUFF((SELECT ' union all '+ c
                    from (SELECT c= 'SELECT  '''+TABLE_NAME+''' as tablename, ISNULL(SUM('+COLUMN_NAME + '),0) AS Total FROM ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'amount' ) p
                       FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,11,'') + ') t'

exec sp_executesql @sql

为每个未知表创建单独的表。为什么不尝试为所有这些表创建一个名为META_TABLE的表，并将所有金额字段添加到该表中？



您可以选择以下方法！

Instead of creating separate table for each unknown table. Why don't you try create one table named META_TABLE for all such tables and add all amount field into that one table?

You can opt below approach!

CREATE TABLE META_TABLE
(
  TABLE_NAME VARCHAR(100),
  NAME       VARCHAR(100),
  AMOUNT   INT
)

INSERT INTO META_TABLE(TABLE_NAME, NAME, AMOUNT)
SELECT 'table1', 'a', 40
UNION ALL
SELECT 'table1', 'b', 60
UNION ALL
SELECT 'table2', 'a', 150
UNION ALL
SELECT 'table2', 'b', 50

SELECT TABLE_NAME, SUM(Amount)
FROM  META_TABLE
GROUP BY TABLE_NAME

这篇关于动态表列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！