【LeetCode2118. 建立方程】 [MySQL group_concat/Oracle LISTAGG/T-SQL Stuff and 'For Xml Path'/PostgreSQL string_agg] 按group拼接字符串，指定分隔符，指定排序顺序

目录

• 题目地址
• 题目描述
• 代码
  • • MySQL
    • Oracle
    • T-SQL
    • PostgreSQL
    • Pandas

题目地址

https://leetcode.cn/problems/build-the-equation/description/

题目描述

Example 2:

输入: 
Terms 表:
+-------+--------+
| power | factor |
+-------+--------+
| 4     | -4     |
| 2     | 1      |
| 1     | -1     |
+-------+--------+
输出: 
+-----------------+
| equation        |
+-----------------+
| -4X^4+1X^2-1X=0 |
+-----------------+

代码

MySQL

with t0 as(
    select power, (case when factor>0 then concat("+",factor) else factor end) as factor
    from  Terms
)
,
t1 as(
    select *,
    (case when power=0 then factor when power=1 then concat(factor,"X") 
    else concat(factor, "X^", power )  end ) as term
    from t0
    order by power desc
)

select concat( group_concat(term order by power desc separator ""),  "=0") as equation
from t1

Oracle

WITH t0 AS (
    SELECT power, 
           CASE WHEN factor > 0 THEN '+' || TO_CHAR(factor) ELSE TO_CHAR(factor) END AS factor
    FROM Terms
),
t1 AS (
    SELECT t0.*, 
           CASE 
               WHEN power = 0 THEN factor 
               WHEN power = 1 THEN factor || 'X' 
               ELSE factor || 'X^' || TO_CHAR(power) 
           END AS term
    FROM t0
    ORDER BY power DESC
)
SELECT LISTAGG(term, '') WITHIN GROUP (ORDER BY power DESC) || '=0' AS equation
FROM t1;

T-SQL

T-SQL好像没有group_concat这个函数，得用点别的办法。
解释看这个帖子 How Stuff and 'For Xml Path' work in SQL Server?。

WITH t0 AS (
    SELECT power, 
           CASE WHEN factor > 0 THEN '+' + CAST(factor AS VARCHAR(MAX)) ELSE CAST(factor AS VARCHAR(MAX)) END AS factor
    FROM Terms
),
t1 AS (
    SELECT *,
           CASE 
               WHEN power = 0 THEN factor 
               WHEN power = 1 THEN factor + 'X'
               ELSE factor + 'X^' + CAST(power AS VARCHAR(MAX))
           END AS term
    FROM t0
)
SELECT CONCAT(
           STUFF((SELECT '' + term 
                  FROM t1 
                  ORDER BY power DESC 
                  FOR XML PATH('')), 1, 0, ''), 
           '=0') AS equation;

PostgreSQL

WITH t0 AS (
    SELECT power, 
           CASE WHEN factor > 0 THEN CONCAT('+', factor::TEXT) ELSE factor::TEXT END AS factor
    FROM Terms
),
t1 AS (
    SELECT *,
           CASE 
               WHEN power = 0 THEN factor 
               WHEN power = 1 THEN CONCAT(factor, 'X') 
               ELSE CONCAT(factor, 'X^', power) 
           END AS term
    FROM t0
    ORDER BY power DESC
)

SELECT CONCAT(string_agg(term::TEXT , '' ORDER BY power DESC), '=0') AS equation
FROM t1;

Pandas

import pandas as pd

def build_the_equation(terms: pd.DataFrame) -> pd.DataFrame:
    # Create a new column 'factor' that adds a "+" sign to positive factors
    terms['factor'] = terms['factor'].apply(lambda x: f"+{x}" if x > 0 else str(x))

    # Create the 'term' column, building the term based on the power
    def build_term(row):
        power = row['power']
        factor = row['factor']
        if power == 0:
            return factor
        elif power == 1:
            return f"{factor}X"
        else:
            return f"{factor}X^{power}"

    terms['term'] = terms.apply(build_term, axis=1)

    # Sort the terms by descending power
    terms_sorted = terms.sort_values('power', ascending=False)

    # Concatenate the terms to form the equation
    equation = ''.join(terms_sorted['term']) + "=0"

    # Create a DataFrame with the equation
    result_df = pd.DataFrame({'equation': [equation]})

    return result_df