SQL 计算商品促销价格

[挑粪水的农民]

--型号，数量，促销编号 ，吊牌价
CREATE TABLE #TAB_A(NUM INT ,Model nvarchar(10),Number INT,Promotion nvarchar(10),price int )

INSERT INTO #TAB_A
SELECT 1,'A',1,'001',199
INSERT INTO #TAB_A
SELECT 2,'A',2,'001',199
INSERT INTO #TAB_A
SELECT 3,'A',1,'001',199
INSERT INTO #TAB_A
SELECT 4,'B',1,'001',268
INSERT INTO #TAB_A
SELECT 5,'B',1,'001',268
INSERT INTO #TAB_A
SELECT 6,'C',1,'001',399
--同一产品买满2件8折，满3件7折，满5件6折
--序号，条件数量 促销折扣 促销编号
CREATE TABLE #TAB_B(NUM INT ,condition INT,sale decimal(18,2),Promotion nvarchar(10) )
INSERT INTO #TAB_B
SELECT 1,'2','0.8','001'
INSERT INTO #TAB_B
SELECT 2,'3','0.7','001'
INSERT INTO #TAB_B
SELECT 3,'5','0.6','001'

现在遇到的问题是A 销售数量是4件，只有3件能打7折，剩余一件不打折，如何在结果集中提现，一眼能看出答案的问题，用SQL写不出来，求救

[挑粪水的农民]

SELECT T2.*,#TAB_B.sale
FROM (
	SELECT Model,SL,T1.Promotion,MAX(condition)AS JS
	FROM #TAB_B
	INNER JOIN (
		SELECT Promotion,Model,SUM(Number)AS SL FROM #TAB_A WHERE Promotion='001'
		GROUP BY Model,Promotion
	) T1 ON #TAB_B.Promotion=T1.Promotion
	WHERE SL>=condition
	GROUP BY Model,SL,T1.Promotion
)AS T2 
INNER JOIN #TAB_B ON T2.promotion=#TAB_B.promotion and t2.JS=#TAB_B.condition

计算好了每个商品能参与的最低折扣，但是在3件打折，1件正价这块不知道怎么去计算

[qq_41327902]

先分组汇总各个产品的出售数量，然后case when几个打折的件数，去计算总价格就好了

[RINK_1]

WITH CTE
AS
(SELECT MODEL,PROMOTION,PRICE,SUM(NUMBER) AS TOTAL FROM #TAB_A
GROUP BY MODEL,PROMOTION,PRICE)

SELECT A.*,
CASE WHEN TOTAL>ISNULL(CONDITION,0) THEN ISNULL(CONDITION,0)*PRICE*ISNULL(SALE,0)+(TOTAL-ISNULL(CONDITION,0))*PRICE
     ELSE TOTAL*PRICE*SALE END AS AMOUNT
FROM CTE A
OUTER APPLY (SELECT TOP 1 * FROM #TAB_B WHERE A.PROMOTION=PROMOTION AND CONDITION<=A.TOTAL ORDER BY CONDITION DESC) AS B

[挑粪水的农民]

引用 5 楼 RINK_1 的回复:

[quote=引用 4 楼 挑粪水的农民 的回复:]
[quote=引用 3 楼 RINK_1 的回复:]

WITH CTE
AS
(SELECT MODEL,PROMOTION,PRICE,SUM(NUMBER) AS TOTAL FROM #TAB_A
GROUP BY MODEL,PROMOTION,PRICE)

SELECT A.*,
CASE WHEN TOTAL>ISNULL(CONDITION,0) THEN ISNULL(CONDITION,0)*PRICE*ISNULL(SALE,0)+(TOTAL-ISNULL(CONDITION,0))*PRICE
     ELSE TOTAL*PRICE*SALE END AS AMOUNT
FROM CTE A
OUTER APPLY (SELECT TOP 1 * FROM #TAB_B WHERE A.PROMOTION=PROMOTION AND CONDITION<=A.TOTAL ORDER BY CONDITION DESC) AS B

大神，能否在不改变#TAB_A 表的情况下，增加一列价格
如
NUM Model Number Promotion price 折后价
1 A 1 001 199
2 A 2 001 199
3 A 1 001 199
4 B 1 001 268
[/quote]

你要折后单价？难道4件商品，你要分成2条记录，3件一条，剩下1件另一条？[/quote]


保留#TAB_A的顺序和结构，只是新增一个列，折后价。
表A是销售列表，我需要将表A的销售物品计算好折后价，不对#TAB_A的数据进行分组作出改变
比如
NUM Model Number Promotion price 折后价
1 A 1 001 199 139.13
2 A 2 001 199 278.26
3 A 1 001 199 199
4 B 1 001 268 268

[RINK_1]

引用 6 楼 挑粪水的农民 的回复:

[quote=引用 5 楼 RINK_1 的回复:] [quote=引用 4 楼 挑粪水的农民 的回复:] [quote=引用 3 楼 RINK_1 的回复:]

WITH CTE
AS
(SELECT MODEL,PROMOTION,PRICE,SUM(NUMBER) AS TOTAL FROM #TAB_A
GROUP BY MODEL,PROMOTION,PRICE)

SELECT A.*,
CASE WHEN TOTAL>ISNULL(CONDITION,0) THEN ISNULL(CONDITION,0)*PRICE*ISNULL(SALE,0)+(TOTAL-ISNULL(CONDITION,0))*PRICE
     ELSE TOTAL*PRICE*SALE END AS AMOUNT
FROM CTE A
OUTER APPLY (SELECT TOP 1 * FROM #TAB_B WHERE A.PROMOTION=PROMOTION AND CONDITION<=A.TOTAL ORDER BY CONDITION DESC) AS B

大神，能否在不改变#TAB_A 表的情况下，增加一列价格 如 NUM Model Number Promotion price 折后价 1 A 1 001 199 2 A 2 001 199 3 A 1 001 199 4 B 1 001 268 [/quote] 你要折后单价？难道4件商品，你要分成2条记录，3件一条，剩下1件另一条？[/quote] 保留#TAB_A的顺序和结构，只是新增一个列，折后价。 表A是销售列表，我需要将表A的销售物品计算好折后价，不对#TAB_A的数据进行分组作出改变 比如 NUM Model Number Promotion price 折后价 1 A 1 001 199 139.13 2 A 2 001 199 278.26 3 A 1 001 199 199 4 B 1 001 268 268[/quote] 你这个折扣的逻辑比较奇特，一般3件打7折，那4件中的第4件也应该享受7折，但是你却是第4件恢复原价。 如果第3条数据是2件，那这条数据的折后单价又应该是多少呢。如果这条是3件呢，那折后单价又应该是多少呢。

[挑粪水的农民]

引用 3 楼 RINK_1 的回复:

WITH CTE
AS
(SELECT MODEL,PROMOTION,PRICE,SUM(NUMBER) AS TOTAL FROM #TAB_A
GROUP BY MODEL,PROMOTION,PRICE)

SELECT A.*,
CASE WHEN TOTAL>ISNULL(CONDITION,0) THEN ISNULL(CONDITION,0)*PRICE*ISNULL(SALE,0)+(TOTAL-ISNULL(CONDITION,0))*PRICE
     ELSE TOTAL*PRICE*SALE END AS AMOUNT
FROM CTE A
OUTER APPLY (SELECT TOP 1 * FROM #TAB_B WHERE A.PROMOTION=PROMOTION AND CONDITION<=A.TOTAL ORDER BY CONDITION DESC) AS B

大神，能否在不改变#TAB_A 表的情况下，增加一列价格
如
NUM Model Number Promotion price 折后价
1 A 1 001 199
2 A 2 001 199
3 A 1 001 199
4 B 1 001 268

[RINK_1]

引用 4 楼 挑粪水的农民 的回复:

[quote=引用 3 楼 RINK_1 的回复:]

WITH CTE
AS
(SELECT MODEL,PROMOTION,PRICE,SUM(NUMBER) AS TOTAL FROM #TAB_A
GROUP BY MODEL,PROMOTION,PRICE)

SELECT A.*,
CASE WHEN TOTAL>ISNULL(CONDITION,0) THEN ISNULL(CONDITION,0)*PRICE*ISNULL(SALE,0)+(TOTAL-ISNULL(CONDITION,0))*PRICE
     ELSE TOTAL*PRICE*SALE END AS AMOUNT
FROM CTE A
OUTER APPLY (SELECT TOP 1 * FROM #TAB_B WHERE A.PROMOTION=PROMOTION AND CONDITION<=A.TOTAL ORDER BY CONDITION DESC) AS B

大神，能否在不改变#TAB_A 表的情况下，增加一列价格 如 NUM Model Number Promotion price 折后价 1 A 1 001 199 2 A 2 001 199 3 A 1 001 199 4 B 1 001 268[/quote] 你要折后单价？难道4件商品，你要分成2条记录，3件一条，剩下1件另一条？