描述:
表
Variables
:+---------------+---------+ | Column Name | Type | +---------------+---------+ | name | varchar | | value | int | +---------------+---------+ 在 SQL 中,name 是该表主键. 该表包含了存储的变量及其对应的值.表
Expressions
:+---------------+---------+ | Column Name | Type | +---------------+---------+ | left_operand | varchar | | operator | enum | | right_operand | varchar | +---------------+---------+ 在 SQL 中,(left_operand, operator, right_operand) 是该表主键. 该表包含了需要计算的布尔表达式. operator 是枚举类型, 取值于('<', '>', '=') left_operand 和 right_operand 的值保证存在于 Variables 表单中.计算表
Expressions
中的布尔表达式。返回的结果表 无顺序要求 。
结果格式如下例所示。
示例 1:
输入: Variables 表: +------+-------+ | name | value | +------+-------+ | x | 66 | | y | 77 | +------+-------+ Expressions 表: +--------------+----------+---------------+ | left_operand | operator | right_operand | +--------------+----------+---------------+ | x | > | y | | x | < | y | | x | = | y | | y | > | x | | y | < | x | | x | = | x | +--------------+----------+---------------+ 输出: +--------------+----------+---------------+-------+ | left_operand | operator | right_operand | value | +--------------+----------+---------------+-------+ | x | > | y | false | | x | < | y | true | | x | = | y | false | | y | > | x | true | | y | < | x | false | | x | = | x | true | +--------------+----------+---------------+-------+ 解释: 如上所示, 你需要通过使用 Variables 表来找到 Expressions 表中的每一个布尔表达式的值.
数据准备:
Create Table If Not Exists Variables (name varchar(3), value int)
Create Table If Not Exists Expressions (left_operand varchar(3), operator ENUM('>', '<', '='), right_operand varchar(3))
Truncate table Variables
insert into Variables (name, value) values ('x', '66')
insert into Variables (name, value) values ('y', '77')
Truncate table Expressions
insert into Expressions (left_operand, operator, right_operand) values ('x', '>', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('x', '<', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'y')
insert into Expressions (left_operand, operator, right_operand) values ('y', '>', 'x')
insert into Expressions (left_operand, operator, right_operand) values ('y', '<', 'x')
insert into Expressions (left_operand, operator, right_operand) values ('x', '=', 'x')
分析:
①观察两个表 不妨将x和y的值 都加到expressions表中
此时就需要使用两个左连接
select left_operand,v1.value as l,operator,right_operand ,v2.value as r from Expressions left join Variables v1 on Expressions.left_operand = v1.name left join Variables v2 on Expressions.right_operand = v2.name
②使用case when 根据操作符operator 列 判断 l 和 r 列值大小 并赋true 和false
select left_operand, operator, right_operand, case when operator = '>' then if(l > r,'true','false') when operator = '<' then if(l < r,'true','false') when operator = '=' then if(l = r,'true','false') end value from t1;
代码:
with t1 as (
select left_operand,v1.value as l,operator,right_operand ,v2.value as r
from Expressions left join Variables v1 on Expressions.left_operand = v1.name
left join Variables v2 on Expressions.right_operand = v2.name)
select
left_operand,
operator,
right_operand,
case
when operator = '>' then if(l > r,'true','false')
when operator = '<' then if(l < r,'true','false')
when operator = '=' then if(l = r,'true','false')
end value
from t1;
总结:
① 一开始走了一些弯路 将两个值连接后 用concat 连接 判断值 后来发现都是true
原因 是 concat连接的都当成字符串 无法计算
② case when 可以用两种解法
解法一:
找出所有true 的条件 然后 else false
case when operator = '>' and l>r then 'true'
when operator = '<' and l<r then 'true'
when operator = '=' and l=r then 'true'
else 'false' end
解法二:
另外一种就是上述代码中 then里面加个if判断 很巧妙 之前没用过