--sources (id, name)---
1, "source one"
2, "source two"
3, "source three"
--first_related_items (value, source_id) ---
0, 1
--second_related_items (value, source_id) ---
0, 2
2, 3
--third_related_items (value, source_id) ---
1, 3
我在其他多个表中都有源行和相关项。如何获得基于其他表的计算列?计算的列将用于源表中的每一行。喜欢
SELECT
sources.name as name,
COMPUTED_VALUE(
first_related_items,
second_related_items,
third_related_items) as result
FROM sources
计算所得的列将像这样派生
if(value for source row in first table == 0){
return 0
}
else if(value for source row in second table == 0){
if(value for source row in third table == 1){
return 1
} else {
return 0
}
} else if(value for source row in third table != NULL){
return value for source row in third table
} else {
return -1
}
您可以连接所有四个表并将此列作为case
表达式计算:
SELECT s.id,
CASE WHEN fri.value = 0 THEN 0
WHEN sri.value = 0 THEN CASE WHEN tri = 1 THEN 1 ELSE 0 END
WHEN tri IS NOT NULL THEN tri
ELSE -1
END AS computed_value
FROM sources s
LEFT JOIN first_related_items fri ON fri.source_id = s.id
LEFT JOIN second_related_items sri ON sri.source_id = s.id
LEFT JOIN third_related_items tri ON tri.source_id = s.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句