C＃和MS访问SQL - 如何创建一个查询，该查询将一个表的两列与另一个表中的一列相加 [英] C# and MS access SQL - how do I create a query that sums 2 columns of one table with one column from another table

问题描述

我是一名初学者，使用C＃.Net连接到MS Access 2016数据库，在Visual Studio 2015中开发WinForms应用程序。我想要做的是添加（SUM，我猜）三列的值：两个来自同一个表，一个列来自另一个。表格结构如下：



--------------------------- ----------------------



表产品：产品，成本1，成本2



表其他成本：成本3



------------------ -------------------------------



我需要一个返回以下内容的查询：



产品，AllCosts



--------- ----------------------------------------



非常感谢你的时间和帮助。我真的很感激。



我尝试了什么：



SELECT产品

来自tbl_products



UNION SELECT价格1 AS价格

来自tbl_products



UNION SELECT价格2 AS价格

来自tbl_products



UNION SELECT Proce3 AS价格

FROM tbl_othercosts;

I am a beginner developing a WinForms application in Visual Studio 2015 using C# .Net connecting to a MS Access 2016 database. What I am trying to do is to add (SUM, I guess) the values of three columns: two from the same table and one column from another. The table structure is as follows:

-------------------------------------------------

Table Products: Product, Cost1, Cost2

Table OtherCosts: Cost3

-------------------------------------------------

I need a query that returns the following:

Product, AllCosts

-------------------------------------------------

Thank you very much for your time and help. I really appreciate it.

What I have tried:

SELECT Product
FROM tbl_products

UNION SELECT Price1 AS Price
FROM tbl_products

UNION SELECT Price2 AS Price
FROM tbl_products

UNION SELECT Proce3 AS Price
FROM tbl_othercosts;

推荐答案

不知何故，你需要加入两个表;因为我们不知道这些表的结构以及它们是如何真正相关的，所以很难提供准确的答案。

这可能是这样的：

Somehow you need to join both tables; as we do not know the structure of these tables and how they are really related, it is hard to provide a precise answer.
This could be something like:

SELECT
  a.Product
 ,a.Price1 + a.Price2 + b.Price3 as 'Total cost'
FROM
 tbl_Products a
 INNER JOIN tbl_othercosts b ON b.ForeignKey = a.PrimaryKey

使用Sum函数和Join：

Use the Sum function with the Join:

SELECT p.Product, SUM(oi.quantity * p.price) AS grand_total,
FROM ORDERITEM oi
    JOIN PRODUCT p ON p.id = oi.productid
        WHERE oi.orderid = @OrderId

这篇关于C＃和MS访问SQL - 如何创建一个查询，该查询将一个表的两列与另一个表中的一列相加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！