Daily exercise - "calculating the value of Boolean expression" LeetCode Plus member exclusive question [detailed analysis] Hive / MySQL

Posted by iimrii on Tue, 21 Dec 2021 23:18:07 +0100

Hello, I'm Lao Wu. You can also call me classmate Wu. Younger friends can also call me senior brother Wu. Welcome to the world of data analysis with me and learn together!

Interested friends can pay attention to me Data analysis column , there are many high-quality articles to share with you.
In addition, you are also welcome to pay attention to my SQL question brushing column , there are high-quality SQL questions I share and detailed analysis.

This blog post is also my SQL problem sharing link. In order to record my problem-solving process and prevent follow-up members from being unable to view the problem again after expiration, I will select a high-quality SQL problem for everyone to publish on my blog every day, and add some of my own problem-solving skills or practical knowledge points, hoping to be helpful to you.

So today's question is question 1440 of LeetCode -- calculating the value of Boolean expression.

The following is a detailed description of the problem.

Problem description

Table Variables:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| name          | varchar |
| value         | int     |
+---------------+---------+
name Is the primary key of the table.
This table contains the stored variables and their corresponding values.

Table Expressions:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| left_operand  | varchar |
| operator      | enum    |
| right_operand | varchar |
+---------------+---------+
(left_operand, operator, right_operand) Is the primary key of the table.
This table contains Boolean expressions that need to be evaluated.
operator Is an enumeration type, Value in('<', '>', '=')
left_operand and right_operand The value of is guaranteed to exist in Variables In the form.

Write an SQL query to evaluate Boolean Expressions in table Expressions

The returned result table has no sequence requirements

The query result format is shown in the following example

Variables surface:
+------+-------+
| name | value |
+------+-------+
| x    | 66    |
| y    | 77    |
+------+-------+

Expressions surface:
+--------------+----------+---------------+
| left_operand | operator | right_operand |
+--------------+----------+---------------+
| x            | >        | y             |
| x            | <        | y             |
| x            | =        | y             |
| y            | >        | x             |
| y            | <        | x             |
| x            | =        | x             |
+--------------+----------+---------------+

Result surface:
+--------------+----------+---------------+-------+
| left_operand | operator | right_operand | value |
+--------------+----------+---------------+-------+
| x            | >        | y             | false |
| x            | <        | y             | true  |
| x            | =        | y             | false |
| y            | >        | x             | true  |
| y            | <        | x             | false |
| x            | =        | x             | true  |
+--------------+----------+---------------+-------+
As shown above, You need to use Variables Table to find Expressions The value of each Boolean expression in the table.

Problem solving ideas

Obtain the left and right operands through two connections;
Then calculate the result according to the operator;
Note result Value is of varchar type and requires if() conversion

code implementation

select e.left_operand,e.operator,e.right_operand,
case e.operator
    when '>' then if(v1.value>v2.value,'true','false')
    when '<' then if(v1.value<v2.value,'true','false')
    else  if(v1.value=v2.value,'true','false')
end as value
from Expressions e
left join Variables v1 on v1.name = e.left_operand 
left join Variables v2 on v2.name = e.right_operand