您好如何创建存储过程以获取五个表的一列总和? [英] hi how to create stored procedure to get five tables one columns total sum?

问题描述

我在我的项目中有5个表，每个表中都包含利润varchar(50)列，所以我想在文本框中进行汇总和显示..有人可以建议我吗?

感谢

hi am having 5 tables in my project in every table contains profit varchar(50) column so i want to sum and display in text box..can any one suggest me?

thanks

推荐答案

-首先，为什么要使用varchar?使用一些数字字段.
-由于一个表包含多个记录，所以我想您不希望所有五个表中的所有记录的该列的总和.
-您可以在存储过程，单个SQL查询语句或五个独立语句中进行操作
-如果有结果，可以将其放在文本框中.

到底是什么问题?

示例:

- First of all, why are you using varchar for that? Use some numeric field.
- As a table contains several records, I suppose you dont want the sum of that column for all records in all five tables.
- You can do it in a stored procedure, a single SQL query statement, or with five standalone statements
- if you have the result you can put it in the textbox.

What is your problem exactly?

Example:

select sum (amount) from
(
select amount from t1
union
select amount from t2
union
select amount from t3
union
select amount from t4
union
select amount from t5
) as sq

即使这不是我真正的新主意建议您远离varchar数据类型.如果将数字存储在数据库中，请使用数字数据类型.这同样适用于所有数据类型.

使用不正确的数据类型时，您只需:
-产生潜在的问题，例如转换不匹配，CPU使用率等.
-实施复杂的SQL
-造成可能的文化问题
-在服务器上创建额外的工作负载，依此类推

正如ZoltánZörgő所说，您可以使用内联视图按需获取所有数据及其总和.但是我也会考虑，您真的需要5个不同的表吗?

这种问题经常是由于不完全归一化而引起的.例如，您能否在表中增加一列来定义行的角色"并使用单个表.

Even though it''s not a new idea I really advice you to move away from varchar data types. If you store numbers in the database, use number data types. The same applies to all data types.

When using improper data types you just:
- create potential problems like conversion mismatches, CPU usage etc.
- enforce complex SQL
- create possible cultural problems
- create extra workload on the server and so on

As Zoltán Zörgő stated, you can use an inline view to get all the data and the sum it as you like. But I would also consider, do you really need 5 different tables?

Very often this kind of question arises because of incomplete normalization. Could you for example have an additional column in your table to define the "role" of the row and use a single table instead.

Declare @test table (COL varchar (20),   COL1 int,   COL2 int,  COL3  int,  COL4 int   )
insert into @test
Select 'AA', 150, 100 , 50  , 4 union all
Select 'HHH', 161 , 125  , 36  , 4 union all
Select 'PPPP',160 , 85 ,75  ,   4 union all
Select 'PPPP',160 , 85 ,75  ,   4 union all
Select 'JJJJJ', 120, 56 , 64 ,  2 union all
Select 'GGGG', 40, 31  ,  9 , 2

Select COL,COL1,COL2,COL3,COL4 from @test
union all
Select 'Total',sum(col1),sum(col2),sum(col3),sum(col4) from @test

这篇关于您好如何创建存储过程以获取五个表的一列总和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！