2118. Build the Equation

SQL架构

Table: Terms

+-------------+------+
| Column Name | Type |
+-------------+------+
| power       | int  |
| factor      | int  |
+-------------+------+
power is the primary key column for this table.
Each row of this table contains information about one term of the equation.
power is an integer in the range [0, 100].
factor is an integer in the range [-100, 100] and cannot be zero.

You have a very powerful program that can solve any equation of one variable in the world. The equation passed to the program must be formatted as follows:

• The left-hand side (LHS) should contain all the terms.
• The right-hand side (RHS) should be zero.
• Each term of the LHS should follow the format "<sign><fact>X^<pow>" where:
  • <sign> is either "+" or "-".
  • <fact> is the absolute value of the factor.
  • <pow> is the value of the power.
• If the power is 1, do not add "^<pow>".
  • For example, if power = 1 and factor = 3, the term will be "+3X".
• If the power is 0, add neither "X" nor "^<pow>".
  • For example, if power = 0 and factor = -3, the term will be "-3".
• The powers in the LHS should be sorted in descending order.

Write an SQL query to build the equation.

The query result format is in the following example.

Example 1:

Input: 
Terms table:
+-------+--------+
| power | factor |
+-------+--------+
| 2     | 1      |
| 1     | -4     |
| 0     | 2      |
+-------+--------+
Output: 
+--------------+
| equation     |
+--------------+
| +1X^2-4X+2=0 |
+--------------+

Example 2:

Input: 
Terms table:
+-------+--------+
| power | factor |
+-------+--------+
| 4     | -4     |
| 2     | 1      |
| 1     | -1     |
+-------+--------+
Output: 
+-----------------+
| equation        |
+-----------------+
| -4X^4+1X^2-1X=0 |
+-----------------+

Follow up: What will be changed in your solution if the power is not a primary key but each power should be unique in the answer?

with  t1 as  (select
`power`,factor,row_number() over(order by power desc) rn  #按 power 降序 标号 便于 后期 group_concat an标号排序拼接
from
Terms
 
) ,t2 as (
select
group_concat(equation order by rn   separator  '') equation
from(
select
if(factor>0,
concat('+',convert(factor,char),
case
when `power` = 0 then ''
when `power` = 1 then 'X'
else concat('X','^',convert(`power`,char))
end),
concat(convert(factor,char),case
when `power` = 0 then ''
when `power` = 1 then 'X'
else concat('X','^',convert(`power`,char))
end))  equation ,rn
from
t1
-- union all
-- select
-- "=0"
) s2
)
select
concat(
(select
equation
from
t2)
 
,"=0") equation

思路一样 稍微简洁了一些:

with t as (
    select power
        ,case 
        when power = 0 then if(factor>0,concat('+',factor),factor)
        when power = 1 then if(factor>0,concat('+',factor,'X'),concat(factor,'X'))
        else if(factor>0,concat('+',factor,'X','^',power),concat(factor,'X','^',power)) end term
    from Terms
    )
select concat(group_concat(term order by power desc separator ''),'=0') equation 
from t 

笔记:

group_concat(equation order by rn   separator  '')

group_concat([Distinct] 要连接的字段 [Order by asc/desc 排序字段] [separator'分隔符'])

其中 要链接的字段 和 排序字段可以不同

CONVERT ( key USING utf8mb4 )
一、转换数据类型
1、语法
input_value –用于指定输入值。
data_type –它用于指定要转换的所需数据类型。

data_type
 1. 字符型，可带参数 : CHAR()  
 2. 日期 : DATE  
 3. 时间: TIME  
 4. 日期时间型 : DATETIME  
 5. 浮点数 : DECIMAL  
 6. 整数 : SIGNED  
 7. 无符号整数 : UNSIGNED

CONVERT( input_value, data_type )

2、实例
SELECT CONVERT(198, CHAR); 
SELECT CONVERT('2019-11-19', DATETIME); 
SELECT CONVERT(2-5, UNSIGNED); 
SELECT CONVERT('geeksforgeeks' USING utf8); 

二、转换字符集的语法
1、语法
input_value –用于指定输入值。
character_set –它用于指定要转换为的所需字符集。

CONVERT( input_value USING character_set )

2、实例
SELECT CONVERT('geeksforgeeks' USING utf8); 
————————————————
版权声明：本文为CSDN博主「姚鑫国」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_44325655/article/details/124489287