表1
产品号 折扣 折扣价 结算价
A01 0.73 455
A02 0.83 776
A03 0.5 543
A04 0.6 754
表2
起始折扣 截止折扣 结算率
0.7 0.89 0.6
0.5 0.69 0.45
结果表
产品号 折扣 折扣价 结算率 结算价
A01 0.73 455 0.6 273
A02 0.83 776 0.6 465.6
A03 0.5 543 0.45 244.35
A04 0.6 754 0.45 339.3
这个语句该怎么写呀…………。想了好久都没办法，只能按表2一行行去处理……。
------解决方案--------------------
- SQL code
if object_id('[t1]') is not null drop table [t1]gocreate table [t1]([产品号] varchar(3),[折扣] numeric(3,2),[折扣价] int,[结算价] numeric(5,2))insert [t1]select 'A01',0.73,455,null union allselect 'A02',0.83,776,null union allselect 'A03',0.5,543,null union allselect 'A04',0.6,754,nullgoif object_id('[t2]') is not null drop table [t2]gocreate table [t2]([起始折扣] numeric(2,1),[截止折扣] numeric(3,2),[结算率] numeric(3,2))insert [t2]select 0.7,0.89,0.6 union allselect 0.5,0.69,0.45goselect t1.产品号,t1.折扣,t1.折扣价,t2.结算率,t1.折扣价*t2.结算率 as 结算价from t1join t2 on t1.折扣 between t2.起始折扣 and t2.截止折扣/**产品号 折扣 折扣价 结算率 结算价---- --------------------------------------- ----------- --------------------------------------- ---------------------------------------A01 0.73 455 0.60 273.00A02 0.83 776 0.60 465.60A03 0.50 543 0.45 244.35A04 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,0union select 'A02',0.83 ,776 ,0union select 'A03',0.5 ,543 ,0union select 'A04',0.6 ,754 ,0insert tb2select 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.000000A02 0.83 776.00 0.00 0.60 465.600000A03 0.50 543.00 0.00 0.45 244.350000A04 0.60 754.00 0.00 0.45 339.300000*/
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDCREATE TABLE tba( 产品号 VARCHAR(10), 折扣 DECIMAL(4,2), 折扣价 INT, 结算价 DECIMAL(10,2))GOINSERT INTO tbaSELECT 'A01', 0.73, 455,0 UNIONSELECT 'A02', 0.83, 776,0 UNIONSELECT 'A03', 0.5, 543,0 UNIONSELECT 'A04', 0.6, 754,0GOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tbb')BEGIN DROP TABLE tbbENDGOCREATE TABLE tbb( 起始折扣 DECIMAL(4,2), 截止折扣 DECIMAL(4,2), 结算率 DECIMAL(4,2))GOINSERT INTO tbbSELECT 0.7, 0.89, 0.6 UNIONSELECT 0.5, 0.69, 0.45GO--SELECT 产品号,折扣,折扣价,折扣价 * (SELECT 结算率 FROM tbb AS B WHERE A.折扣 BETWEEN B.起始折扣 AND B.截止折扣)--FROM tba AS AUPDATE tba SET 结算价 = 折扣价 * (SELECT 结算率 FROM tbb AS B WHERE tba.折扣 BETWEEN B.起始折扣 AND B.截止折扣) SELECT * FROM tba产品号 折扣 折扣价 结算价A01 0.73 455 273.00A02 0.83 776 465.60A03 0.50 543 244.35A04 0.60 754 339.30
------解决方案--------------------
- SQL code
--产品号 折扣 折扣价 结算价 --A01 0.73 455 --A02 0.83 776 --A03 0.5 543 --A04 0.6 754 CREATE TABLE #A( 产品号 NVARCHAR(50), 折扣 FLOAT, 折扣价 FLOAT, 结算价 FLOAT)CREATE TABLE #B( 起始折扣 FLOAT, 截止折扣 FLOAT, 结算率 FLOAT) INSERT INTO #ASELECT 'A01',0.73,455,NULL UNION SELECT 'A02', 0.83, 776 ,NULL UNION SELECT 'A03', 0.5 ,543 ,NULL UNION SELECT 'A04', 0.6 ,754,NULLINSERT INTO #BSELECT 0.7,0.89,0.6 UNIONSELECT 0.5,0.69,0.45 SELECT a.产品号,a.折扣,a.折扣价,b.结算率,b.结算率*a.折扣价 结算价 FROM #A aJOIN #B b ON a.折扣 BETWEEN b.起始折扣 AND b.截止折扣DROP TABLE #ADROP TABLE #B--A01 0.73 455 0.6 273--A02 0.83 776 0.6 465.6--A03 0.5 543 0.45 244.35--A04 0.6 754 0.45 339.3