力扣sql中等篇练习(十七)

力扣sql中等篇练习(十七)

1 计算布尔表达式的值

1.1 题目内容

1.1.1 基本题目信息1

1.1.2 基本题目信息2

1.1.3 示例输入输出

a 示例输入

b 示例输出

1.2 示例sql语句

# 使用SELECT子句中的子查询查询到对应的值
SELECT t1.left_operand,t1.operator,t1.right_operand,
       case when t1.operator='>' AND t1.v1>t1.v2 then 'true'
            when t1.operator='=' AND t1.v1=t1.v2 then 'true'
            when t1.operator='<' AND t1.v1<t1.v2 then 'true'
       else 'false'
       end value
FROM
(
    SELECT e.left_operand,e.operator,e.right_operand,
        (SELECT value FROM Variables WHERE name=e.left_operand) v1,
        (SELECT value FROM Variables WHERE name=e.right_operand) v2
    FROM Expressions e
)t1

1.3 运行截图

2 计算税后工资

2.1 题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

a.示例输入

b.示例输出

2.2 示例sql语句

# ①先求出每个公司最高的工资②根据去显示对应税率③计算税后工资
SELECT s.company_id,s.employee_id,s.employee_name,ROUND(salary*(1-t2.r),0) salary
FROM Salaries s
INNER JOIN
(
    SELECT t1.company_id,
        case when t1.hSalary<1000 then 0
                when t1.hSalary>=1000 AND t1.hSalary<=10000 then 0.24
                else 0.49
                end r
    FROM
    (
        SELECT company_id,max(salary) hSalary
        FROM Salaries
        GROUP BY company_id
    )t1
)t2
ON s.company_id=t2.company_id

2.3 运行截图

3 最近的三笔订单

3.1 题目内容

3.1.1 基本题目信息1

3.1.2 基本题目信息2

3.1.3 示例输入输出

a 示例输入

b 示例输出

3.2 示例sql语句

# 降序就外面小于里面 前n条就 where子句中 n+1>
SELECT c1.name customer_name,c1.customer_id,t1.order_id,t1.order_date
FROM Customers c1
INNER JOIN
(
    SELECT o.*
    FROM Orders o
    WHERE 4>
    (
        SELECT count(*)
        FROM Orders o1
        WHERE o.order_date<=o1.order_date AND o.customer_id=o1.customer_id
    )
)t1
ON c1.customer_id=t1.customer_id
ORDER BY customer_name asc,customer_id asc,order_date desc

3.3 运行截图