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 thefactor
.<pow>
is the value of thepower
.
- If the power is
1
, do not add"^<pow>"
.- For example, if
power = 1
andfactor = 3
, the term will be"+3X"
.
- For example, if
- If the power is
0
, add neither"X"
nor"^<pow>"
.- For example, if
power = 0
andfactor = -3
, the term will be"-3"
.
- For example, if
- 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