从2个表的数据计算总价

2021-09-10 00:00:00 sql tsql sql-server view

我有如下数据:

表 SALE:

PR_KEY TRAN_ID   TRAN_NO  TRAN_DATE   CUSTOMER_ID USER_ID TABLE_ID PAY_TYPE_ID TOTAL_PRICE  
-------------------------------------------------------------------------------------------
187    SALE    130511164  2012-05-27    0000       ADMIN        59        1          0

表 Sale_detail

PR_KEY   FR_KEY    LIST_ORDER    ITEM_ID    PRICE     AMOUNT
--------------------------------------------------------------
281      187        0.0000       9          10000        3
282      187        0.0000       tom        20000        2

我的问题是:

  1. 有什么方法可以通过计算(SD.Price * SD.Amount)+(SD.Price * SD.Amount) 将数据推送到sale.totalprice(对于相同的FR_KEY)

或者创建一个显示总价的视图，同样的计算(SD.Price * SD.Amount)+ (SD.Price * SD.Amount)(对于每个FR_KEY)

Or create a view to show the total price, same calculation (SD.Price * SD.Amount)+ (SD.Price * SD.Amount) (for each FR_KEY)

这是针对 SQL Server 的，sale_detail 的 FR_KEY 引用了 Sale 表的 Pr_key.

This is for SQL Server, and FR_KEY of sale_detail references the Pr_key of the Sale table.

我试过了 -

SELECT dbo.SALE_DETAIL.PR_KEY,
dbo.SALE_DETAIL.FR_KEY, 
SUM(dbo.SALE_DETAIL.PRICE * dbo.SALE_DETAIL.AMOUNT) AS Grand_total 
FROM dbo.SALE_DETAIL, dbo.SALE JOIN SALE s 
      on s.PR_KEY = SALE_DETAIL.FR_KEY 
WHERE SALE_DETAIL.PR_KEY = @FR_KEY 
ORDER BY PR_KEY

我想要的结果是:

PR_KEY TRAN_ID USER_ID TABLE_ID PAY_TYPE_ID TOTAL_PRICE
187    SALE    ADMIN   59       1            70000

推荐答案

您可以尝试在下面查询第二个问题.

You can try query below for your second question.

SELECT
    SALE.PR_KEY,
    TRAN_ID,
    TRAN_NO,
    TRAN_DATE,
    CUSTOMER_ID,
    USER_ID,
    TABLE_ID,
    PAY_TYPE_ID,
    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

相关文章