mysql一个查询只有多个选择

如下表所示：
| 新ID|年份|识别号|新价值|
| - ------| - ------| - ------| - ------|
| 1个|小行星2020|一百一十一|五十|
| 1个|小行星2020|一百一十一|六十|
| 1个|小行星2021|一百一十一|七十|
| 1个|小行星2021|一百一十二|二十个|
| 1个|小行星2021|一百一十二|四十|
| 1个|小行星2022|一百一十三|三十|
| 1个|小行星2022|一百一十三|八十|
| 第二章|小行星2020|二百二十二|二十个|
| 第二章|小行星2020|二百二十三|十个|
| 第二章|小行星2021|二百二十三|四十|
| 第二章|小行星2021|二百二十四|十个|
| 第二章|小行星2021|二百二十四|九十|
| 第二章|小行星2021|二百二十四|九十九|
| 第二章|小行星2022|二百二十五|十个|
| 第二章|二○二三|二百二十五|五十|
给定上面的示例表，我需要在mysql中创建一个新表的单个查询，该新表将在第一列中具有newID值的列表，并且在第二列中将具有针对每个newID在表中呈现的不同年份，并且在第三列中，我将具有由以下规则给出的称为diff_cum_year的值：
1.如果对于每个newID值，year列中存在的年份是最小的年份，则diff_cum_year的值将由相同newID值和相同年份值的每个不同ID值的最大newValues之和给出
1.如果对于具有相同newID值的year列中存在的每个值，我只有一个ID值，并且该ID值已经存在于具有等于year-1的year值的相同newID值，则diff_cum_year的值将是相同newID和相同年份的newValue的最大值减去具有等于year-1的year值的diff_cum_year的值。1表示相同的newID
1.另一方面，如果对于在year列中存在的具有相同newID值的每一年，我只有一个ID值，并且这是在具有相同newID并且年值uagual到year-1的ID中不存在的ID值，则diff_cum_year的值将是针对相同newID预测的年值的newValue字段的最大值
1.如果对于具有相同newID值的year列中的每个年份，我具有多个ID值，则该值将是相同newID的每个不同ID值的最大newValue值的总和减去diff_cum_year的值，其中year等于相同newID的year -1
输出表应该如下所示：
| 新ID|年份|年累计差异|
| - ------| - ------| - ------|
| 1个|小行星2020| 60 [规则1最大值（50，60）]|
| 1个|小行星2021| 50 [规则4最大值（70）+最大值（20，40）-60（diff_cum_year的先前值）]|
| 1个|小行星2022| 80 [规则3最大值（30，80）]|
| 第二章|小行星2020| 30 [规则1最大值（20）+最大值（10）]|
| 第二章|小行星2021| 109 [规则4最大值（40）+最大值（10，90，99）-30（diff_cum_year的先前值）]|
| 第二章|小行星2022|十个|
| 第二章|二○二三|四十|

解决这个问题有一个巧妙的方法，下面是这个解决方案的步骤：

生成关于三元组的“newValore”的最大值
获取每对〈newID，year_〉的最大值的总和
减去连续几年存在的ID的总和
在所有可用值中获取最小总和（因为减法是我们执行的最后一个操作，所以最小总和将是我们需要的最新生成值）

这些操作中的每一项都是在单独的子查询中完成的：

WITH max_vals AS (
    SELECT DISTINCT newId, 
                    year_, 
                    ID, 
                    MAX(newValore) OVER(PARTITION BY newID, year_, ID) AS max_value
    FROM tab
), sum_max_vals AS (
    SELECT *, SUM(max_value) OVER(PARTITION BY newId, year_) AS sum_max_value
    FROM max_vals
), sum_max_vals_with_subs AS(
    SELECT newID, 
           year_,
           sum_max_value - 
               CASE WHEN LAG(year_) OVER(PARTITION BY ID ORDER BY year_) = year_-1
                    THEN LAG(sum_max_value) OVER(PARTITION BY ID ORDER BY year_)
                    ELSE 0 
               END AS diff_cum_year
    FROM sum_max_vals
)
SELECT newID, 
       year_,
       MIN(diff_cum_year) AS diff_cum_year
FROM sum_max_vals_with_subs
GROUP BY newID, year_

检查here演示。

mysql一个查询只有多个选择

1条答案

相关问题

热门标签

最新问答