我想显示来自dbo的total_amount.最终输出表中的订单.如何?

Question

CREATE PROCEDURE proc_assign_tier_for_rewards(
@email varchar(255))
AS
BEGIN
SELECT tier_name, reward,min_amount,max_amount, CONCAT(min_amount, '-', max_amount) AS range
FROM reward_tier
WHERE (SELECT SUM(total_amount)
FROM dbo.orders
WHERE company_email = @email
) BETWEEN min_amount AND max_amount
END
GO

Answer 1

如下所示使用JOIN以获得所需的输出。在SELECT语句中增加.total_amount返回total_amount值

CREATE PROCEDURE proc_assign_tier_for_rewards(
@email varchar(255))
AS
BEGIN

SELECT tier_name, 
reward, 
min_amount, 
max_amount, 
CONCAT(min_amount, '-', max_amount) AS range, 
o.total_amount
FROM reward_tier rt
JOIN (SELECT SUM(total_amount) AS total_amount
FROM dbo.orders
WHERE company_email = @email
) o
ON o.total_amount BETWEEN rt.min_amount AND rt.max_amount
END