代码之家 › 专栏 › 技术社区 › Doonie Darkoo

如何分配金额百分比项目

sql-server

Doonie Darkoo · 技术社区 · 6 年前

我有一个存储订单的表结构。假设我有一个客户要求3的订单 Items 不同的 Qty 和不同 Unit Price 每个项目。店主给了他 25000 全部折扣 items 但是后来,客户来了,要求去掉一个 item 从订单上,所以我想得到 amount 属于 discount 那是适用的 项目 以便 数量 属于 折扣 适用于此 项目 也可以根据订单进行调整。

CREATE TABLE SALE_MASTER
(
ORDER_NUMBER VARCHAR(50) NOT NULL PRIMARY KEY,
DISCOUNT_AMOUNT NUMERIC
)

INSERT INTO SALE_MASTER VALUES ('SO-100-ORD-19', 25000);

CREATE TABLE SALE_DETAIL
(
ORDER_NUMBER VARCHAR(50),
ITEM_NAME VARCHAR(250),
QUANTITY INT,
PRICE NUMERIC,
CONSTRAINT FK_SALE_MASTER FOREIGN KEY (ORDER_NUMBER)
REFERENCES SALE_MASTER(ORDER_NUMBER)
)
INSERT INTO SALE_DETAIL VALUES ('SO-100-ORD-19', 'Double Bed', 5, 70000);
INSERT INTO SALE_DETAIL VALUES ('SO-100-ORD-19', 'Sofa', 5, 10000);
INSERT INTO SALE_DETAIL VALUES ('SO-100-ORD-19', 'Dining Table', 1, 50000);

SELECT * FROM SALE_MASTER
SELECT ITEM_NAME, QUANTITY, PRICE, (DISCOUNT_AMOUNT/(QUANTITY*PRICE)) FROM SALE_DETAIL, SALE_MASTER
WHERE SALE_DETAIL.ORDER_NUMBER = SALE_MASTER.ORDER_NUMBER

1 回复 | 直到 6 年前

Zhorov 6 年前

如果我正确理解您的情况,您需要计算一种货币单位(25000/450 000)的折扣,然后进行所有计算:

;
WITH cte AS (
    SELECT 
        SALE_MASTER.ORDER_NUMBER,
        SUM(SALE_DETAIL.QUANTITY * SALE_DETAIL.PRICE) AS TOTAL_AMOUNT,
        SALE_MASTER.DISCOUNT_AMOUNT
    FROM SALE_DETAIL, SALE_MASTER
    WHERE SALE_DETAIL.ORDER_NUMBER = SALE_MASTER.ORDER_NUMBER
    GROUP BY SALE_MASTER.ORDER_NUMBER, SALE_MASTER.DISCOUNT_AMOUNT
)
SELECT 
    SALE_DETAIL.ITEM_NAME, 
    SALE_DETAIL.QUANTITY, 
    SALE_DETAIL.PRICE, 
    (SALE_DETAIL.QUANTITY * SALE_DETAIL.PRICE) * (cte.DISCOUNT_AMOUNT / cte.TOTAL_AMOUNT) AS ITEM_DISCOUNT_FOR_ALL_ITEMS,
    SALE_DETAIL.PRICE * (cte.DISCOUNT_AMOUNT / cte.TOTAL_AMOUNT) AS ITEM_DISCOUNT_FOR_ONE_ITEM
FROM SALE_DETAIL, cte
WHERE SALE_DETAIL.ORDER_NUMBER = cte.ORDER_NUMBER

输出:

ITEM_NAME      QUANTITY PRICE   ITEM_DISCOUNT_FOR_ALL_ITEMS  ITEM_DISCOUNT_FOR_ONE_ITEM
Double Bed     5        70000   19444.444444                 3888.888889
Sofa           5        10000   2777.777778                  555.555556
Dining Table   1        50000   2777.777778                  2777.777778

推荐文章