if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([产品号] varchar(3),[折扣] numeric(3,2),[折扣价] int,[结算价] numeric(5,2))
insert [t1]
select 'A01',0.73,455,null union all
select 'A02',0.83,776,null union all
select 'A03',0.5,543,null union all
select 'A04',0.6,754,null
go
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([起始折扣] numeric(2,1),[截止折扣] numeric(3,2),[结算率] numeric(3,2))
insert [t2]
select 0.7,0.89,0.6 union all
select 0.5,0.69,0.45
go

select t1.产品号,t1.折扣,t1.折扣价,t2.结算率,t1.折扣价*t2.结算率  as 结算价
from t1
join t2 on t1.折扣 between t2.起始折扣 and t2.截止折扣

/**
产品号  折扣                                      折扣价         结算率                                     结算价
---- --------------------------------------- ----------- --------------------------------------- ---------------------------------------
A01  0.73                                    455         0.60                                    273.00
A02  0.83                                    776         0.60                                    465.60
A03  0.50                                    543         0.45                                    244.35
A04  0.60                                    754         0.45                                    339.30

(4 行受影响)
**/

------解决方案--------------------
SQL code
create table tb1(产品号 varchar(12),折扣 money, 折扣价 money, 结算价 money)
create table tb2(起始折扣 money,截止折扣 money, 结算率 money)

insert tb1 

select 'A01', 0.73,455,0
union select 'A02',0.83 ,776 ,0
union select 'A03',0.5 ,543 ,0
union select 'A04',0.6 ,754 ,0
insert tb2
select 0.7,0.89,0.6 
union select 0.5,0.69,0.45 



select *,case when 折扣 between 0.70 and 0.89 then 0.60  when 折扣 between 0.50 and 0.69 then 0.45 else 0 end 结算率 ,
            case when 折扣 between 0.70 and 0.89 then 0.60 *折扣价 when 折扣 between 0.50 and 0.69 then 0.45*折扣价 else 0 end 结算价
            from tb1 

/*
产品号    折扣    折扣价    结算价    结算率    结算价
A01    0.73    455.00    0.00    0.60    273.000000
A02    0.83    776.00    0.00    0.60    465.600000
A03    0.50    543.00    0.00    0.45    244.350000
A04    0.60    754.00    0.00    0.45    339.300000
*/

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
CREATE TABLE tba
(
    产品号 VARCHAR(10),
    折扣 DECIMAL(4,2),
    折扣价 INT,
    结算价 DECIMAL(10,2)
)
GO
INSERT INTO tba
SELECT 'A01', 0.73, 455,0 UNION
SELECT 'A02', 0.83, 776,0 UNION
SELECT 'A03', 0.5, 543,0 UNION
SELECT 'A04', 0.6, 754,0
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')
BEGIN
    DROP TABLE tbb
END
GO
CREATE TABLE tbb
(
    起始折扣 DECIMAL(4,2),
    截止折扣 DECIMAL(4,2),
    结算率 DECIMAL(4,2)
)
GO
INSERT INTO tbb
SELECT 0.7, 0.89, 0.6 UNION
SELECT 0.5, 0.69, 0.45

GO

--SELECT 产品号,折扣,折扣价,折扣价 * (SELECT 结算率 FROM tbb AS B WHERE A.折扣 BETWEEN B.起始折扣 AND B.截止折扣)
--FROM tba AS A

UPDATE tba SET 结算价 = 折扣价 * (SELECT 结算率 FROM tbb AS B WHERE tba.折扣 BETWEEN B.起始折扣 AND B.截止折扣) 


SELECT * FROM tba

产品号    折扣    折扣价    结算价
A01    0.73    455    273.00
A02    0.83    776    465.60
A03    0.50    543    244.35
A04    0.60    754    339.30

------解决方案--------------------
SQL code

--产品号 折扣 折扣价 结算价 
--A01 0.73 455 
--A02 0.83 776 
--A03 0.5 543 
--A04 0.6 754 

CREATE TA