从2个表的数据计算总价

或者创建一个显示总价的视图，同样的计算(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

这篇关于从2个表的数据计算总价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！