SELECT COALESCE(grouped_wells.groupforecasting_id,wells.id) as the_id, string_agg(wells.name,', ') as well_name, sum(gas_cd) as gas_cd, date
FROM productions
INNER JOIN completions on completions.id = productions.completion_id
INNER JOIN wellbores on wellbores.id = completions.wellbore_id
INNER JOIN wells on wells.id = wellbores.well_id
INNER JOIN fields on fields.id = wells.field_id
INNER JOIN clusters on clusters.id = fields.cluster_id
LEFT JOIN grouped_wells on grouped_wells.wells_id = wells.id
LEFT JOIN groupforecasting on groupforecasting.id = grouped_wells.groupforecasting_id and groupforecasting.workspace_id = 3
GROUP BY the_id, productions.date
ORDER BY the_id, productions.date
在上面的 SQL 中，我根據the_id來自COALESCE. 我想避免grouped_wells.groupforecasting_id與wells.id.
一種可能的方法是根據我從中獲得的兩個值中的哪一個添加前綴COALESCE，添加“group_”或“well_”。
如何將此條件前綴添加到the_id？
如果grouped_wells.groupforecasting_id為 Null，則將“well_”連接到 的結果COALESCE。
否則將“group_”連接到COALESCE.
uj5u.com熱心網友回復：
你幾乎已經找到了解決方案
如果 grouped_wells.groupforecasting_id 為 Null，則將“well_”連接到 COALESCE 的結果。否則將“group_”連接到 COALESCE 的結果。
在 postgres 中，您可以使用 case when 語法：
case when ... then ...
else ...
end as the_id
在您的選擇（和您的評論）中完成它：
SELECT
case when grouped_wells.groupforecasting_id is NULL then concat('well_', wells.id)
else
concat('group_', grouped_wells.groupforecasting_id)
end as the_id,
string_agg(wells.name,', ') as well_name,
sum(gas_cd) as gas_cd, date
FROM productions
INNER JOIN completions on completions.id = productions.completion_id
INNER JOIN wellbores on wellbores.id = completions.wellbore_id
INNER JOIN wells on wells.id = wellbores.well_id
INNER JOIN fields on fields.id = wells.field_id
INNER JOIN clusters on clusters.id = fields.cluster_id
LEFT JOIN grouped_wells on grouped_wells.wells_id = wells.id
LEFT JOIN groupforecasting on groupforecasting.id = grouped_wells.groupforecasting_id and groupforecasting.workspace_id = 3
GROUP BY the_id, productions.date
ORDER BY the_id, productions.date
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/363139.html
標籤：sql PostgreSQL的