从2表SQL Server计算价格总和 [英] Calculate sum of price from 2 table sql server

问题描述

询问后，计算总价格的总和这里及其工作，但随后我添加了（SUM（PRICE * AMOUNT）AS TOTAL_PRICE）以创建自己的视图，Total_price = Expr1，仅是PRICE * AMOUNT，这里没有总和。

After asking about calculate the sum of the total price here and Its work but then went I add(SUM(PRICE*AMOUNT) AS TOTAL_PRICE) to create my own View, the Total_price = Expr1, It just PRICE * AMOUNT, there is no sum here.

我应用它来创建视图
，这里是查询

I apply It to create a view here is the query

    SELECT                       SALE_DETAIL.PR_KEY,SALE_DETAIL.FR_KEY,dbo.SALE_DETAIL.PRICE * dbo.SALE_DETAIL.AMOUNT AS Expr1, 
      
                         dbo.DM_ITEM.UNIT_ID, dbo.DM_ITEM.ACTIVE AS ACTIVE_ITEM, SUM(PRICE*AMOUNT) AS TOTAL_PRICE , dbo.SALE.TOTAL_CHAIR_USE,SALE_DETAIL.ITEM_ID,dbo.SALE_DETAIL.PRICE
FROM                     dbo.DM_ITEM 
                         INNER JOIN
                         dbo.SALE_DETAIL ON dbo.DM_ITEM.ITEM_ID = dbo.SALE_DETAIL.ITEM_ID 
                         LEFT OUTER JOIN
                         dbo.DM_VAT_TAX ON dbo.SALE_DETAIL.VAT_TAX_ID = dbo.DM_VAT_TAX.VAT_TAX_ID 
                         LEFT OUTER JOIN
                         dbo.SALE 
                         LEFT OUTER JOIN
                         dbo.DM_TABLES ON dbo.SALE.TABLE_ID = dbo.DM_TABLES.TABLE_ID ON dbo.SALE_DETAIL.FR_KEY = dbo.SALE.PR_KEY
WHERE                   (dbo.SALE.IS_PROVISIONAL = 1)
GROUP BY
    SALE.PR_KEY,DM_ITEM.ITEM_NAME,DM_ITEM.ITEM_CLASS_ID,DM_ITEM.UNIT_ID,
    SALE.TABLE_ID,DM_ITEM.ACTIVE,
    SALE.TRAN_ID,SALE_DETAIL.PRICE,
    TRAN_NO,PAY_TYPE_ID,DM_VAT_TAX.VAT_TAX_NAME,
    TRAN_DATE,SALE.COMMENT,
    CUSTOMER_ID,SALE_DETAIL.PR_KEY,SALE_DETAIL.FR_KEY,SALE_DETAIL.LIST_ORDER,SALE_DETAIL.ITEM_ID,SALE_DETAIL.VAT_TAX_ID,
    USER_ID,SALE.TOTAL_CUSTOMER_PAY,SALE.EXPORT_TYPE_ID,SALE.IS_PROVISIONAL,
    DM_TABLES.TABLE_NAME,DM_TABLES.TOTAL_CHAIR,SALE_DETAIL.AMOUNT,SALE.TOTAL_CHAIR_USE,SALE.COST_OTHER,SALE_DETAIL.HOUR_CALL

这是输出

PR_KEY FR_KEY Expr1 UNIT_ID ACTIVE_ITEM TOTAL_PRICE TOTAL_CHAIR_USE ITEM_ID PRICE


 272.00 180.00  710000   7  0           710000      0               LAU_BO  355000.00
273.00  182.00  50000    5  0           50000       0               LON_R   50000.00
274.00  182.00  180000   5  0           180000      0               G_L     180000.00
281.00  187.00  30000    3  0           30000       0               9       10000.00
282.00  187.00  40000    3  0           40000       0               tom     20000.00
283.00  189.00  200000   3  0           200000      0               2       200000.00
295.00  199.00  0.00     0  1           0.00        0               5         0.00
296.00  199.00  0.00     5  0           0.00        0               B_XT    0.00

任何人都可以

推荐答案

根据您在此处的问题以及您在上一个问题中提供的选择，将价格总和从 Sale_Detail 推到主 Sale 表现在看起来更合乎逻辑。您可以通过使用带有 WITH 函数的上一个查询来 UPDATE （推送）主表。这样，您可以根据 Sale 表的主键（它是 Sale_Detail 中的外键）更新总价。我了解。

According to your problem here and the choices you provided on your previous question, pushing sum of prices from Sale_Detail to master Sale table seems more logical now. You can UPDATE (push) your master table by using the previous query with WITH function. This way, you can update total price in Sale table based on its primary key which is a foreign key in Sale_Detail as I understood.

您可以根据关键字和其他列值的总价格总计在Sale_Detail表中调整分组。

You can adjust your grouping in Sale_Detail table according to your total price summation for the key and other column values.

请注意用影响可读性的语法编写和发布查询。

Please be careful writing and posting queries with such syntax that affects the readability.

WITH Sales_CTE (PR_KEY, TOTAL_PRICE)
 AS
 (
SELECT
    Sale.PR_KEY,
    SUM(PRICE*AMOUNT) AS TOTAL_PRICE
FROM
    Sale_Detail
INNER JOIN Sale 
    ON Sale_Detail.FR_KEY = Sale.PR_KEY
GROUP BY
    Sale.PR_KEY,
    TRAN_ID,
    TRAN_NO,
    TRAN_DATE,
    CUSTOMER_ID,
    USER_ID,
    TABLE_ID,
    PAY_TYPE_ID
)
UPDATE
    Sale
SET
    Sale.TOTAL_PRICE = Sales_CTE.TOTAL_PRICE
FROM
    Sale
INNER JOIN
    Sales_CTE
ON 
    Sales_CTE.PR_KEY = Sale.PR_KEY;

这篇关于从2表SQL Server计算价格总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！