在线求个的SQL语句……大侠快来……

[rekym]

表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一行行去处理……。

[忘世麒麟]

请给出这两个表的联系,比如说摸一个列是否是共享的.否则也看不出结果怎么来的啊.

[开着拖拉机泡妞]

不说算法，看不懂你要干什么

[--小F--]

没看懂意思。

[百年树人]

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 行受影响)
**/

[rekym]

表1有商品折扣

表2有折扣范围选用表2的新折扣算出结算价

如A01 它的折扣是0.73 在表2中属于 0.7~~0.89 范围 选用0.6的新折扣

[开着拖拉机泡妞]

--> 测试数据：[表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 all
select 'A02',0.83,776  union all
select 'A03',0.5,543 union all
select '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 all
select 0.5,0.69,0.45

select a.*,b.结算率*a.折扣价 as [结算价]  from [表1] a
cross join [表2] b
where a.[折扣] between b.起始折扣 and b.截止折扣


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

[Felixzhaowenzhong]

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
*/


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

/*
产品号	折扣	折扣价	结算率	结算价
A01	0.73	455.00	0.60	273.00
A02	0.83	776.00	0.60	465.60
A03	0.50	543.00	0.45	244.35
A04	0.60	754.00	0.45	339.30
*/

[孤独加百列]

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

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

[rekym]

谢啦，发现自己也会，脑袋没转过来……。

[koumingjie]

create table 表1
(
 产品号 varchar(3),
 折扣 float,
 折扣价 float, 
 结算价 float
)

insert into 表1
select 'A01', 0.73,455,null union all
select 'A02', 0.83,776,null union all 
select 'A03', 0.5,54,null   union all
select 'A04', 0.6,754,null


create table 表2
(
 起始折扣 float,
 截止折扣 float,
 结算率 float	
)

insert into 表2
select 0.7, 0.89, 0.6   union all
select 0.5, 0.69, 0.45 


select T.产品号,T.折扣,T.折扣价,T.结算率,T.折扣价 * T.结算率  AS 结算价 from (
select *,结算率=(select b.结算率  from 表2 b where a.折扣 between b.起始折扣 and b.截止折扣 ) from 表1 a) T

产品号  折扣                     折扣价                    结算率                    结算价
---- ---------------------- ---------------------- ---------------------- ----------------------
A01  0.73                   455                    0.6                    273
A02  0.83                   776                    0.6                    465.6
A03  0.5                    54                     0.45                   24.3
A04  0.6                    754                    0.45                   339.3

[koumingjie]

[Quote=引用 9 楼 的回复:]
谢啦，发现自己也会，脑袋没转过来……。
[/Quote]

晕，费半天劲建表