有二张表(数据库是sql2000)
a表
a_id a_name a_money
001 北京 2200
002 上海 4500
003 南京 2000
004 广东 10002
b表
a b c
0 2000 0.06
2001 5000 0.05
5001 10000 0.04
10001 1000000 0.03
b表的意思如下：
0到2000，按百分之六奖励
2001到5000，按百分之五奖励
5001到10000，按百分之四奖励
10001以上，按百分之三奖励
要查询的结果如下：
a_id a_name a_money a_bonus
001 北京 2200 110
002 上海 4500 225
003 南京 2000 120
004 广东 10002 300.06
------解决思路----------------------
select a.*, a.a_money*b.c from a表 a, b表 b where a.a_money between b.a and b.b
------解决思路----------------------
SELECT A.A_id,
A.A_NAME,
A.A_MONEY,
A.A_MONEY*B.C AS A_BONUS
FROM A JOIN B
ON ,A.A_MONEY>B.A AND,A.A_MONEY<= B.B
------解决思路----------------------
CREATE TABLE #a(a_id CHAR(3),a_name CHAR(4),a_money int)
INSERT #a
SELECT '001','北京',2200 UNION ALL
SELECT '002','上海',4500 UNION ALL
SELECT '003','南京',2000 UNION ALL
SELECT '004','广东',10002
CREATE TABLE #b(a int,b int,c DECIMAL(3,2))
INSERT #b
SELECT 0,2000,0.06 UNION ALL
SELECT 2001,5000,0.05 UNION ALL
SELECT 5001,10000,0.04 UNION ALL
SELECT 10001,1000000,0.03
SELECT a.*,CAST(a.a_money*(SELECT c FROM #b WHERE a.a_money BETWEEN a AND b) AS INT)
FROM #a a
------解决思路----------------------
CREATE TABLE #a(a_id CHAR(3),a_name CHAR(4),a_money int)
INSERT #a
SELECT '001','北京',2200 UNION ALL
SELECT '002','上海',4500 UNION ALL
SELECT '003','南京',2000 UNION ALL
SELECT '004','广东',10002
CREATE TABLE #b(a int,b int,c DECIMAL(3,2))
INSERT #b
SELECT 0,2000,0.06 UNION ALL
SELECT 2001,5000,0.05 UNION ALL
SELECT 5001,10000,0.04 UNION ALL
SELECT 10001,1000000,0.03
select a.*,a.a_money*b.c
from #a a
inner join #b b
on a.a_money between b.a and b.b
------解决思路----------------------
select a.a_id,a.a_name,a.a_money,a.a_money*b.c as a_bonus
from a,b
where a.a_money between b.a and b.b
------解决思路----------------------
create table a(a_id int,a_name varchar(32),a_money int)
create table b(a int,b int,c float)
insert into a(a_id,a_name,a_money) values(001,'北京',2200),
(002,'上海',4500),(003,'南京',2000),(004,'广东',10002)
insert into b(a,b,c) values(0,2000,0.06),(2000,5000,0.05),
(5001,10000,0.04),(10001,1000000,0.03)
go
select * from a
select * from b
go
select a.a_id,a.a_name,a.a_money,(a.a_money*b.c) as a_bonus from a,b
where a.a_money >= b.a and a.a_money<b.b
已测
------解决思路----------------------
全部代码放在一个窗口，然后按ctrl+m，再一次性执行全部代码，会出现一系列的图，看上面的百分比，百分比越低的通常效率越高。但是即使你找到了最低的那个，在没有考虑数据选择性、索引、编码方式等问题之前，也很难说它就是最好的。