在线求个的SQL语句……大侠快来……
表1
产品号折扣折扣价结算价
A010.73455
A020.83776
A030.5543
A040.6754
表2
起始折扣截止折扣结算率
0.70.890.6
0.50.690.45
结果表
产品号折扣折扣价结算率结算价
A010.734550.6273
A020.837760.6465.6
A030.55430.45244.35
A040.67540.45339.3
这个语句该怎么写呀…………。想了好久都没办法，只能按表2一行行去处理……。
[解决办法]
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 行受影响)**/
[解决办法]
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*/
[解决办法]
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
[解决办法]
--产品号 折扣 折扣价 结算价 --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
[解决办法]
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*/select t1.产品号,t1.折扣,t1.折扣价,t2.结算率,t1.折扣价*t2.结算率 as 结算价from tb1 t1join tb2 t2 on t1.折扣 between t2.起始折扣 and t2.截止折扣/*产品号 折扣 折扣价 结算率 结算价A01 0.73 455.00 0.60 273.00A02 0.83 776.00 0.60 465.60A03 0.50 543.00 0.45 244.35A04 0.60 754.00 0.45 339.30*/
[解决办法]
--> 测试数据：[表1]if object_id('[表1]') is not null drop table [表1]create table [表1]([产品号] varchar(3),[折扣] numeric(6,2),[折扣价] int)insert [表1]select 'A01',0.73,455 union allselect 'A02',0.83,776 union allselect 'A03',0.5,543 union allselect 'A04',0.6,754 --> 测试数据：[表2]if object_id('[表2]') is not null drop table [表2]create table [表2]([起始折扣] numeric(6,2),[截止折扣] numeric(6,2),[结算率] numeric(6,2))insert [表2]select 0.7,0.89,0.6 union allselect 0.5,0.69,0.45select a.*,b.结算率*a.折扣价 as [结算价] from [表1] across join [表2] bwhere a.[折扣] between b.起始折扣 and b.截止折扣/*产品号 折扣 折扣价 结算价A01 0.73 455 273.00A02 0.83 776 465.60A03 0.50 543 244.35A04 0.60 754 339.30*/