表1
Commissions MoneyLevel
--------------------- ---------------------
0.20 2000.00
0.10 1000.00表2
AutoID TotalAmount
---------------- ---------------------
1 1000.00
3 2000.00
10 2000.00
11 2000.00
12 1000.00
13 1000.00
14 2000.00
15 2000.00
16 2000.00
18 1990.00
30 2000.00有2个表, 表2的TotalAmount是大于等于1000但是小于2000的就用0.1比例, 如果大于等于2000的就用0.2的比例, 如此类推, 请问这个sql怎么写.谢谢.
Commissions MoneyLevel
--------------------- ---------------------
0.20 2000.00
0.10 1000.00表2
AutoID TotalAmount
---------------- ---------------------
1 1000.00
3 2000.00
10 2000.00
11 2000.00
12 1000.00
13 1000.00
14 2000.00
15 2000.00
16 2000.00
18 1990.00
30 2000.00有2个表, 表2的TotalAmount是大于等于1000但是小于2000的就用0.1比例, 如果大于等于2000的就用0.2的比例, 如此类推, 请问这个sql怎么写.谢谢.
解决方案 »
- 为什么我SQLSERVER登录成功后什么错误都错作不了,只要一操作就会报这个错误?应该如何去解决?
- 请教各位大虾一个菜问题,谢谢
- 做期初库存,左边要列出所有商品,不管它当前在库存表中有没有记录。这样的SQL应该怎么写?
- 求解一个方案
- 求一条SQL语句,个人应当很有难度,具体见内!谢谢!
- SQL server2005如何恢复数据库?
- sysobjects的xtype 代表什么含义?这里共有9种!
- 子陌红尘《《感谢钻钻的答案!不过现在还是不对滴!有钻的帖子要加分滴!
- 历史数据表里面每次计算大概有200万的数据量,希望大家有好建议。
- 求汉字拼码??
- 各位大哥帮忙啊,急,想在函数中取存储的值,有什么办法
- 修改
from 表2 ,表1
where 表2.TotalAmount >= 表1.MoneyLevel
group by 表2.AutoID, 表2.TotalAmount
Commissions MoneyLevel_1 MoneyLevel_2
--------------------- ---------------------
0.20 2001.00 *****
0.10 1000.00 2000
when TotalAmount >= 2000 and TotalAmount < 3000 then TotalAmount * 0.2
...如此写完。
end
from tb2
以下方法不包含小于1000,或者大于3000的.(也就是你的最小值或最大值)
create table t1(Commissions decimal(18,2),MoneyLevel decimal(18,2))
insert into t1 values(0.20 , 2000.00 )
insert into t1 values(0.10 , 1000.00 )
create table t2(AutoID int, TotalAmount decimal(18,2))
insert into t2 values(1 , 1000.00 )
insert into t2 values(3 , 2000.00 )
insert into t2 values(10, 2000.00 )
insert into t2 values(11, 2000.00 )
insert into t2 values(12, 1000.00 )
insert into t2 values(13, 1000.00 )
insert into t2 values(14, 2000.00 )
insert into t2 values(15, 2000.00 )
insert into t2 values(16, 2000.00 )
insert into t2 values(18, 1990.00 )
insert into t2 values(30, 2000.00 )
goselect t2.* , t2.TotalAmount * t1.Commissions
from t2 , t1
where cast(t2.TotalAmount as int) / 1000 = cast(t1.MoneyLevel as int) / 1000drop table t1 , t2/*
AutoID TotalAmount
----------- -------------------- ---------------------------------------
1 1000.00 100.0000
3 2000.00 400.0000
10 2000.00 400.0000
11 2000.00 400.0000
12 1000.00 100.0000
13 1000.00 100.0000
14 2000.00 400.0000
15 2000.00 400.0000
16 2000.00 400.0000
18 1990.00 199.0000
30 2000.00 400.0000(所影响的行数为 11 行)*/
insert into t1 values(0.20 , 2000.00 )
insert into t1 values(0.10 , 1000.00 )
create table t2(AutoID int, TotalAmount decimal(18,2))
insert into t2 values(0 , 800.00 )
insert into t2 values(1 , 1000.00 )
insert into t2 values(3 , 2000.00 )
insert into t2 values(10, 2000.00 )
insert into t2 values(11, 2000.00 )
insert into t2 values(12, 1000.00 )
insert into t2 values(13, 1000.00 )
insert into t2 values(14, 2000.00 )
insert into t2 values(15, 2000.00 )
insert into t2 values(16, 2000.00 )
insert into t2 values(18, 1990.00 )
insert into t2 values(30, 2000.00 )
insert into t2 values(40, 3000.00 )
goselect t.* ,
case when TotalAmount < (select min(MoneyLevel) from t1) then TotalAmount * (select top 1 Commissions from t1 order by MoneyLevel)
when TotalAmount >= (select max(MoneyLevel) from t1) then TotalAmount * (select top 1 Commissions from t1 order by MoneyLevel desc)
else TotalAmount * (select Commissions from t1 where cast(MoneyLevel as int) / 1000 = cast(t.TotalAmount as int) / 1000)
end
from t2 tdrop table t1 , t2/*
AutoID TotalAmount
----------- -------------------- ---------------------------------------
0 800.00 80.0000
1 1000.00 100.0000
3 2000.00 400.0000
10 2000.00 400.0000
11 2000.00 400.0000
12 1000.00 100.0000
13 1000.00 100.0000
14 2000.00 400.0000
15 2000.00 400.0000
16 2000.00 400.0000
18 1990.00 199.0000
30 2000.00 400.0000
40 3000.00 600.0000(所影响的行数为 13 行)
*/