这样的 SQL 语句该怎么写

实际的情况是一共有两张表TEST_A, TEST_B

TEST_A: account_id, profit_rate, score
TEST_B: account_id, profit, init_funds

TEST_B表的数据都有

account_id 是关联主键

TEST_A为空
profit_rate = profit / init_funds
score = profit_rate > 0.3 ? 5 : profit_rate > 0.18 ? 4 : profit_rate > 0.12 ? 3 : 0

问题是：
如何使用一条SQL生成TEST_A表的数据

Supplement 2 · Oct 17, 2014

考虑 init_funds为0的情况

profit / init_funds只计算一次

语句

大于

字段

22 replies • 2014-10-17 13:31:12 +08:00

lichao

Oct 17, 2014

select a, b, case when a/b > 0.5 then 5 when a/b > 0.3 then 4 when a/b > 0.12 then 3 else 0 end as score

CosWind

Oct 17, 2014

@lichao 诶，这样 a/b会算几次？

lichao

Oct 17, 2014

@CosWind 最影响数据库性能的是磁盘 IO，数学计算与之相比可忽略不计

tobyzw

Oct 17, 2014

select a, b, case when a/b > 0.5 then 5 when a/b > 0.3 then 4 when a/b > 0.12 then 3 else 0 end as score
-------------------------
可能会有问题，a/b可能会出异常，b=0的情况需要考虑进去

CosWind

Oct 17, 2014

@lichao 诶。我测试的结果是80w的表，这样写和a/b设置成常量1计算出来的结果所需要的时间分别是1.01s 和 0.85s，差别还是有的

ozking

Oct 17, 2014

用a > 0.5*b 会不会效率高一点？

CosWind

Oct 17, 2014

@tobyzw 是的。

CosWind

Oct 17, 2014

@xudshen 条件有多个。

CosWind

Oct 17, 2014

@lichao 额。。我这测试不科学。

ozking

Oct 17, 2014

@CosWind 你可以把a/b的值存起来，像这样的sql肯定也不是仅仅run一次的，与其每次都计算，不如一次先搞定

CosWind

Oct 17, 2014

@xudshen 对，我想要的就是这个效果，a/b的计算只计算一次，但是我想只用一条SQL达成目的

ozking

Oct 17, 2014

@CosWind 在insert的时候就包括a/b，或者设置个insert,update的trigger

frye

Oct 17, 2014

SELECT
a,
b,
IF(
a / b > 0.12,
IF(
a / b > 0.18,
IF(
a / b > 0.3,
5,
4
),
3
),
0
) AS score
FROM
table_name

cye3s

Oct 17, 2014 via Android

子查询查一次a/b，外面套case

frye

Oct 17, 2014

INSERT INTO TEST_A (account_id, profit_rate, score) SELECT
account_id,

IF (
init_funds > 0,
profit / init_funds,
0
) AS profit_rate,

IF (

IF (
init_funds > 0,
profit / init_funds,
0
) > 0.12,

IF (

IF (
init_funds > 0,
profit / init_funds,
0
) > 0.18,

IF (

IF (
init_funds > 0,
profit / init_funds,
0
) > 0.3,
5,
4
),
3
),
0
) AS score
FROM
TEST_B

CosWind

Oct 17, 2014

@cye3s 子查询性能会不会差一点

CosWind

Oct 17, 2014

@frye a/b 能否只算一次呢

frye

Oct 17, 2014

INSERT INTO TEST_A (account_id, profit_rate, score) SELECT
TEST_B.account_id,
TEST_B.profit_rate,

IF (
TEST_B.profit_rate > 0.12,

IF (
TEST_B.profit_rate > 0.18,

IF (TEST_B.profit_rate > 0.3, 5, 4),
3
),
0
) AS score
FROM
(
SELECT

IF (
init_funds > 0,
profit / init_funds,
0
) AS profit_rate,
account_id
FROM
TEST_B
) AS TEST_B

CosWind

Oct 17, 2014

@frye 这样用子查询，感觉有点得不偿失。

viquuu

Oct 17, 2014

select a,b,
case when c > 0.5 then 5 when c > 0.3 then 4 when c > 0.12 then 3 else 0 end as score
from (
select a,b, a/isnull(b,1) as c from test
)

frye

Oct 17, 2014

@CosWind 本就没有必要去纠结 [profit / init_funds只计算一次] 这个问题

CosWind

Oct 17, 2014

@frye 恩。我只是想钻个牛角尖，想看看有没有利用@变量能实现，或者其它方式实现的方法 >_<`